Question

ISO datetime import to SQL db


Badge

Good day,

 

I have a list of warnings that I want to write to a database. I am using MSSQQL_JDBC_NONSPATIAL writer to write to our SQL Server database. One of the table attribute is the WarningTimestamp. (I set the type to datetime in the writer.) It is in ISO datetime format. When I want to write the records to the db, I always get an error telling me "MS SQL Server (JDBC) Writer: Error writing feature. Error: Attribute 'WarningTimestamp' does not contain a valid timestamp" even thought the WarningTimestamp is '2018-10-08T00:00:00'.

 

If I use the DateTimeConverter to convert the ISO datetime to FME datetime, the import is working. Why?

 

Please help.

 

 


19 replies

Userlevel 4
Badge +25

What I'm reading is that the SQL Server format for datetime is YYYY-MM-DD HH:MM:SS

I imagine that it is the "T" character that is causing a problem. Try it without (use a StringReplacer to replace T with a space character) and see if that works.

As for the FME datetime, I am guessing that FME knows the value should be a datetime (because you set the column type in the writer) so automatically tries to convert the supplied value. It guesses the incoming date is FME format because that's the default. It won't automatically convert the supplied value from ISO because it doesn't know that's what the format is.

As FME gets smarter in time, it'll probably be able to recognize that an attribute is a date, and what format it is, and so oddities like this wouldn't happen. But that's a development to look forward to in the future...

Userlevel 2
Badge +17

As far as I know, most (maybe all) FME writers accept a date/time value only with Standard FME Date/Time Format when its destination data type is set to a date/time type in the writer feature type. Then, the writer implicitly converts its format appropriately when writing it out to the destination dataset.

You don't need to know date/time formats for each specific data format, always can use Standard FME Date/Time Format regardless of what the destination data format is. I think it's a common design concept of FME writers.

Badge

My next question is: I have the value in ISO date time format (2018-11-26T00:00:00) and as it wasn't working I converted to FME datetime (20181126000000) and then I import to sql db. In the database the value is 2018-11-26 12:00:00.000

Why is the time '2018-11-26 12:00:00.000' and not '2018-11-26 00:00:00.000' as it is midnight and not noon. (see ISO date time)?

Badge

As far as I know, most (maybe all) FME writers accept a date/time value only with Standard FME Date/Time Format when its destination data type is set to a date/time type in the writer feature type. Then, the writer implicitly converts its format appropriately when writing it out to the destination dataset.

You don't need to know date/time formats for each specific data format, always can use Standard FME Date/Time Format regardless of what the destination data format is. I think it's a common design concept of FME writers.

My next question is: I have the value in ISO date time format (2018-11-26T00:00:00) and as it wasn't working I converted to FME datetime (20181126000000) and then I import to sql db. In the database the value is 2018-11-26 12:00:00.000

Why is the time '2018-11-26 12:00:00.000' and not '2018-11-26 00:00:00.000' as it is midnight and not noon. (see ISO date time)?

Userlevel 4
Badge +25

My next question is: I have the value in ISO date time format (2018-11-26T00:00:00) and as it wasn't working I converted to FME datetime (20181126000000) and then I import to sql db. In the database the value is 2018-11-26 12:00:00.000

Why is the time '2018-11-26 12:00:00.000' and not '2018-11-26 00:00:00.000' as it is midnight and not noon. (see ISO date time)?

How are you reading that back? Is that what it shows in the database with a SQL query, or it that what the FME Data Inspector shows when you read it back with FME? I'd be interested to know if both techniques show the same result. Sometimes it's more that how you inspect the results can vary what it shows (though of course sometimes it's what is going in that is the problem, and in that case maybe set the time specifically to 20181126130000 in FME to see what that produces).

Userlevel 4
Badge +25

What I'm reading is that the SQL Server format for datetime is YYYY-MM-DD HH:MM:SS

I imagine that it is the "T" character that is causing a problem. Try it without (use a StringReplacer to replace T with a space character) and see if that works.

As for the FME datetime, I am guessing that FME knows the value should be a datetime (because you set the column type in the writer) so automatically tries to convert the supplied value. It guesses the incoming date is FME format because that's the default. It won't automatically convert the supplied value from ISO because it doesn't know that's what the format is.

As FME gets smarter in time, it'll probably be able to recognize that an attribute is a date, and what format it is, and so oddities like this wouldn't happen. But that's a development to look forward to in the future...

So our developers tell me that in the future FME writers will be able to take either ISO or FME dates and handle them correctly. That's some way off. Currently only a handful of formats will do that (and JDBC-based ones like this are not part of that list).

Badge

My next question is: I have the value in ISO date time format (2018-11-26T00:00:00) and as it wasn't working I converted to FME datetime (20181126000000) and then I import to sql db. In the database the value is 2018-11-26 12:00:00.000

Why is the time '2018-11-26 12:00:00.000' and not '2018-11-26 00:00:00.000' as it is midnight and not noon. (see ISO date time)?

I just read back from the db. When I read with the SQL Reader it is : 20181126120000 and when I read with the SQL JDBC reader it is: 20181126120000.000000000

They read it with a dif precision but in both cases it is still noon.

Badge

So our developers tell me that in the future FME writers will be able to take either ISO or FME dates and handle them correctly. That's some way off. Currently only a handful of formats will do that (and JDBC-based ones like this are not part of that list).

But is the non JDBC SQL writer capable of using ISO format for writing?

Userlevel 4

My next question is: I have the value in ISO date time format (2018-11-26T00:00:00) and as it wasn't working I converted to FME datetime (20181126000000) and then I import to sql db. In the database the value is 2018-11-26 12:00:00.000

Why is the time '2018-11-26 12:00:00.000' and not '2018-11-26 00:00:00.000' as it is midnight and not noon. (see ISO date time)?

The database timestamp value is always a floating point number internally, not a string. When you see "2018-11-26 12:00:00.000" that isn't the internal value, but simply your database client that automatically converts the timestamp to something i hope will be useful to you.

Badge

My next question is: I have the value in ISO date time format (2018-11-26T00:00:00) and as it wasn't working I converted to FME datetime (20181126000000) and then I import to sql db. In the database the value is 2018-11-26 12:00:00.000

Why is the time '2018-11-26 12:00:00.000' and not '2018-11-26 00:00:00.000' as it is midnight and not noon. (see ISO date time)?

Yes, it displays 2018-11-26 12:00:00.000 in sql server. But the problem is that the time is noon (12:) and not midnight (00:). I still haven't found the solution, why the FME datetime converter puts it to noon even though midnight is specified for iso datetime.

Badge

My next question is: I have the value in ISO date time format (2018-11-26T00:00:00) and as it wasn't working I converted to FME datetime (20181126000000) and then I import to sql db. In the database the value is 2018-11-26 12:00:00.000

Why is the time '2018-11-26 12:00:00.000' and not '2018-11-26 00:00:00.000' as it is midnight and not noon. (see ISO date time)?

Hi @kat

could you please inspect the value right before your SQL Server Writer to confirm it is 20181126000000? How do you inspect your SQL Server database to see 20181126 12:00:00.000?

We definitely need to investigate this issue.

Badge

As far as I know, most (maybe all) FME writers accept a date/time value only with Standard FME Date/Time Format when its destination data type is set to a date/time type in the writer feature type. Then, the writer implicitly converts its format appropriately when writing it out to the destination dataset.

You don't need to know date/time formats for each specific data format, always can use Standard FME Date/Time Format regardless of what the destination data format is. I think it's a common design concept of FME writers.

This is absolutely correct: every Writer understands (and accepts only) values in FME datetime format. This helps avoiding confusion and data misinterpretation.

Userlevel 4

Yes, it displays 2018-11-26 12:00:00.000 in sql server. But the problem is that the time is noon (12:) and not midnight (00:). I still haven't found the solution, why the FME datetime converter puts it to noon even though midnight is specified for iso datetime.

Could it possibly be some AM/PM issue? Does any of your servers use that notation in their locale settings?

Userlevel 2
Badge +17

Hello everyone. I am trying to transform KML parcel data into cityGML GenericCityObject and get as close as possible to a sample file I was given. I am particularly interested in the LOT_KEY attribute. When I transform it, I manage to get it into the attributes and that is all fine. But I can't understand why boundeBy happens outside of the  GenericCityOject. I need it to be also within as the sample file I also has it. Does anyone know what am I missing?  citygml_feature_role in my file shows cityOjectMember and in the sample file boundedBy.

This is my file:

<?xml version="1.0" encoding="UTF-8"?>
<core:CityModel xmlns:brid="http://www.opengis.net/citygml/bridge/2.0" xmlns:tran="http://www.opengis.net/citygml/transportation/2.0" xmlns:frn="http://www.opengis.net/citygml/cityfurniture/2.0" xmlns:wtr="http://www.opengis.net/citygml/waterbody/2.0" xmlns:sch="http://www.ascc.net/xml/schematron" xmlns:veg="http://www.opengis.net/citygml/vegetation/2.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:tun="http://www.opengis.net/citygml/tunnel/2.0" xmlns:tex="http://www.opengis.net/citygml/texturedsurface/2.0" xmlns:gml="http://www.opengis.net/gml" xmlns:gen="http://www.opengis.net/citygml/generics/2.0" xmlns:dem="http://www.opengis.net/citygml/relief/2.0" xmlns:app="http://www.opengis.net/citygml/appearance/2.0" xmlns:luse="http://www.opengis.net/citygml/landuse/2.0" xmlns:xAL="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0" xmlns:bldg="http://www.opengis.net/citygml/building/2.0" xmlns:smil20="http://www.w3.org/2001/SMIL20/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:smil20lang="http://www.w3.org/2001/SMIL20/Language" xmlns:pbase="http://www.opengis.net/citygml/profiles/base/2.0" xmlns:core="http://www.opengis.net/citygml/2.0" xmlns:grp="http://www.opengis.net/citygml/cityobjectgroup/2.0">
<gml:boundedBy>
<gml:Envelope srsName="EPSG:4326" srsDimension="3">
<gml:lowerCorner>1.26871270440194 103.819343318131 0</gml:lowerCorner>
<gml:upperCorner>1.26971645830091 103.819958991181 0</gml:upperCorner>
</gml:Envelope>
</gml:boundedBy>
<core:cityObjectMember>
<gen:GenericCityObject gml:id="UUID_e246f60b-0e9f-4f74-bbb3-deb96b7efa52">
<gen:stringAttribute name="integer">
<gen:value>1</gen:value>
</gen:stringAttribute>
<gen:intAttribute name="tessellate">
<gen:value>-1</gen:value>
</gen:intAttribute>
<gen:intAttribute name="extrude">
<gen:value>0</gen:value>
</gen:intAttribute>
<gen:intAttribute name="visibility">
<gen:value>-1</gen:value>
</gen:intAttribute>
<gen:stringAttribute name="LOT_KEY">
<gen:value>MK01-00121W</gen:value>
</gen:stringAttribute>
<gen:stringAttribute name="INC_CRC">
<gen:value>ECC760583B0F6674</gen:value>
</gen:stringAttribute>
<gen:stringAttribute name="FMEL_UPD_D">
<gen:value>20181106010036</gen:value>
</gen:stringAttribute>
<gen:stringAttribute name="snippet">
<gen:value></gen:value>
</gen:stringAttribute>
<gen:lod1Geometry>
<gml:Polygon srsName="EPSG:4326" srsDimension="3">
<gml:exterior>
<gml:LinearRing>
<gml:posList>1.26896420626564 103.819343318131 0 1.2692578540151 103.819423555662 0 1.26971645830091 103.819571721534 0 1.26946016330691 103.819958991181 0 1.26882149973169 103.819765450217 0 1.26871270440194 103.819723758803 0 1.26896420626564 103.819343318131 0</gml:posList>
</gml:LinearRing>
</gml:exterior>
</gml:Polygon>
</gen:lod1Geometry>
</gen:GenericCityObject>
</core:cityObjectMember>
</core:CityModel>

Screenshot 2021-03-11 at 10.10.24 AMScreenshot 2021-03-11 at 9.28.48 AM 

Badge

Hi @kat

could you please inspect the value right before your SQL Server Writer to confirm it is 20181126000000? How do you inspect your SQL Server database to see 20181126 12:00:00.000?

We definitely need to investigate this issue.

To see the sql result I use SSMS.

 

The value before writing is following (using inspector):

 

1.step to convert to iso format: 2018-12-03T00:00:00 and then

 

2.step convert iso to fme datetine as iso not working: 20181203000000
Badge

How did you convert the ISO datetime to FME datetime?

Was the resulting value exactly '20181126000000' immediately after the conversion before writing?

The value before writing is following (using inspector):

 

1.step to convert to iso format: 2018-12-03T00:00:00 and then

 

2.step convert iso to fme datetine using DateTimeConverter as iso not working: 20181203000000
Badge

The value before writing is following (using inspector):

 

1.step to convert to iso format: 2018-12-03T00:00:00 and then

 

2.step convert iso to fme datetine using DateTimeConverter as iso not working: 20181203000000

Hi @kat

I have reproduced the problem. If a midnight value is written into datetime type column, the value becomes noon of the same day. At the same time, datetime2 and datetimeoffset columns store correct (midnight) values.

I apologize for the the inconvenience caused by this problem. We now have bug FMEENGINE-57690 filed in our system and will get the problem fixed.

Thank you for bringing this to our attention.

Badge

So our developers tell me that in the future FME writers will be able to take either ISO or FME dates and handle them correctly. That's some way off. Currently only a handful of formats will do that (and JDBC-based ones like this are not part of that list).

As of the latest FME 2019.0 betas (19168+) JDBC writers can consume extended ISO dates and times.

Badge

My next question is: I have the value in ISO date time format (2018-11-26T00:00:00) and as it wasn't working I converted to FME datetime (20181126000000) and then I import to sql db. In the database the value is 2018-11-26 12:00:00.000

Why is the time '2018-11-26 12:00:00.000' and not '2018-11-26 00:00:00.000' as it is midnight and not noon. (see ISO date time)?

As @LenaAtSafe noted, we have a bug here. It turns out to only affect the new(ish) Bulk mode, which was added in FME 2018 and is on by default in FME 2019. Expect this to be fixed in the next release of FME 2018.x.

For the moment you can work around it by

  1. not using bulk mode or
  2. using datetime2 columns instead of datetime columns

Reply