Skip to main content
  • EN
I'm converting an xml-document to a SQL Server table and 'm having trouble with a datetime field.

 

 

The input value is: 2013-01-02T15:11:44.390+01:00

 

The Sql Server field type is: datetime2

 

 

I guess I should use a DateFormatter, but I'm not sure how to configure this. Tried a lot, but nothing works.

 

 

Message:

 

MS SQL Server (Spatial) Writer: Attribute `datum' of feature type `dbo.DateTable' has specified type `datetime2', but existing database table field has type `datetime'. Information may be lost during conversion

 

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

Reply


Community Stats

30,094
Posts
114,252
Replies
37,725
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.