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
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
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
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
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?
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?
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