Convert xml date to SQL Server datetime2

Hi Koen,

 

 

Blue coloured log text is mostly a warning, in your case the datetime2 will be converted to datime while writting.

 

A warning also means that the values are usually written, can you confirm that? (you dont need to use the date formatter for that)

 

 

Itay

No, the values are not written.

 

 

For example, I have as input:

 

<MainNode>  <DatumInfo>   <id>6</id>   <Datum>2013-01-02T15:11:44.390+01:00</Datum>  </DatumInfo>   <DatumInfo>   <id>7</id>   <Datum>2013-01-02</Datum>  </DatumInfo> </MainNode>

 

 

 

This has to be written to SQL server, where the date field is of type datetime2.

 

 

When I don't use a DateFormatter, nothing is written.

 

 

When I use a DateFormatter with FME Date/Time (%Y%m%d%H%M%S), only the second item is written, the first one is not written to SQL Server

 

 

 

Hi Koen,

 

 

The DateFormatter assumes the souce string is a standard date time format when Source Format parameter is not specified. The first item "2013-01-02T15:11:44.390+01:00" maybe is not a standard format the DateFormatter can recognize.

 

Try confirming whether the DateFormatter formatted the string correctly.

 

 

Takashi

Hi Koen,

 

 

I agree with Takashi on that, the datetime annotation is not recognized.

 

It does however work if you split the string with "." and use the first part of the list (e.i 2013-01-02T15:11:44)

 

The second sub string (390+01:00) can  be of importance, does the input xml comes with any metadata that can shed some light about the datetime annotation?

Itay's solution of splitting string works(Substring(0,18)), but then obviously some information('390+01:00') is lost.

 

 

As far as I know the '390' is the fraction of a second

 

and '+01:00' is the timezone.

 

That's pretty standard isn't it? Doesn't FME support that?

Hi Koen,

 

 

In the SQL Server, "datetime2" type can represent the fraction part of second, and "datetimeoffset" type can represent both of the fraction and the timezone.

 

But the SQL Server Writer (MSSQL_ADO) of FME currently doesn't seem to support writing those data type values - if I was wrong, please correct.

 

Those values can be inserted into existing table using  the SQLExecutor (SQL insert statement).

 

 

Takashi