Question

Datetime formatting for SQL to SQL Translation - DatetimeParse or DateTimeConverter ?

  • 14 April 2021
  • 6 replies
  • 82 views
A
Badge

There is nothing in the FME universe that has me pulling my hairs out as much over and over again as DateTime formatting and conversion.

 

I realize I might be be at one extreme end of the FME user spectrum in that In connect to a very small subset of data source/target types. 90% of my work is reading and writing to/from spatial and non-spatial SQL databases. Which means I don't have much use for the full range of date/time manipulation available in FME.

 

But that makes it even more annoying when I read datetime() from SQL and write datetime() to SQL and suddenly find myself puzzling why any formatting or transformation is necessary!? Then, there are just as many occasions when this does not happen and I immediately forget the steps I took to get things to work.

 

As a recent example, I'm reading a number of date fields using:

  • FeatureReader, Type: Microsoft SQL Server Non-Spatial

Then I write some of this data back to SQL using:

  • SQL Executor: Microsoft SQL Server Non-Spatial

 

Now why this wouldn't be a straight source-target read-write translation without any need to even touch the date fields,  puzzles me. But I understand that FME will read datetime() and convert it to string. Which means at some point before writing back to a SQL database, I need to convert. string back to datetime.

 

I tried doing so using various permutations of the following inside my SQL statement in the SQL Executor:

@Value(@DateTimeParse(@Value(MYDATE),%Y-%m-%d-%H:%M:%S))

Thinking this would create something SQL Server would understand. But it only resulted in all my dates defaulting to: "1900-01-01 00:00:00.0".

 

What eventually did work was inserting a DateTimeConverter in front of the SQL Executor and telling it to expect dates in FME input format and to convert them to:

%Y-%m-%dT%H:%M:%S

Why is it that this works in the DateTimeConverter but not with DateTimeParse, is the latter the wrong method?

 

This of course, blows up for any NULL values. Telling the Converter to passthrough Null, creates more   "1900-01-01 00:00:00.0" with my SQL statement. So I tried various variations of Testers or CASE statements inside the SQL. Finally, I settled for a few lines of "cleanup" SQL after the main SQL statement that just replaces all "1900-01-01" dates with NULL.

 

Should this have been such a meandering odyssey of trial and error? Haha. I guess what I'm getting at is that I suspect there is a better way and I want to know what that is. I love working with FME and usually find work-around for problems I run into. In fact, it' rarely the more complex workbenches that cause headaches. It's these little day-to-day head scratches that get in the way. It drives me bonkers when I don't know why FME does what it does and  I have to rely on work-arounds that I'm not sure are really needed.

QUESTIONS

  1. How do you convert the FME datetime string correctly to something SQL Server will accept and do so inside the writer?
  2. What is the simplest way  to get FME to leave NULL datetimes alone and just insert NULL?
  3. Under what circumstances (I realize this is broad), does this issue even raise its ugly head? I seem to build workbenches all the time that move dates around and never run into any of this?

Thanks for any feedback or insights or link to helpful explainers. (I'm sure I've read 9/10 FME pages on this subject and still think I'm missing something.)

 

6 replies

nielsgerrits
Rank
Userlevel 6
Badge +32

Why is it that this works in the DateTimeConverter but not with DateTimeParse, is the latter the wrong method?

If I read the documentation correctly, @DateTimeParse() is meant to do something else than you expect from it:

Parses arbitrary strings containing date time information into datetime strings in FME format.

Can be used for validation and repair. Can parse leap seconds by carrying the extra second forward so that all parts are brought in range.

 Not sure if this depends on the writer format, but I think the writers I work with expect FME date \\ FME datetime \\ FME datetime with offset. But I don't use SQL Server, so not sure for your case.

 

How do you convert the FME datetime string correctly to something SQL Server will accept and do so inside the writer?

I use the DateTimeConverter.

 

What is the simplest way to get FME to leave NULL datetimes alone and just insert NULL?

DateTimeConverter, parameter Passthrough nulls, empties or missing = Yes.

 

Under what circumstances (I realize this is broad), does this issue even raise its ugly head? I seem to build workbenches all the time that move dates around and never run into any of this?

I think I have had most issues with saving dates to datetimefields combined with daylight saving time.

Also local time on a server and webviewers doing smart things with dst and timezones can be confusing. Our best practice is to save UTC and let the webviewer do it's smart thing.

A
Badge

Thanks for taking the time for this response. I had begun wondering if the DateTimeParse was if not only redundant, maybe the wrong way to go. I think where I'm having issues with the NULLs is not specific to Dates. So I'll spawn another thread for that.

M
Badge +2

@agelfert​ Thanks for sharing your frustration.  Here's the summary of how FME is behaving:

  • FME readers (including those in FeatureReader) will return the standard FME datetime format (i.e.20111202220144) 
  • A SELECT in SQLExecutor or SQLCreator that returns a datetime data type will return the standard FME datetime format (not the database datetime string).
  • Any other SQL query (in SQLexecutor/Creator, SQL To Run After Write etc.) that references a datetime field will have to use the databases preferred datetime string format (i.e. 2011-12-02 22:01:44.000 for SQL Server, PostGIS, Oracle). 

So as you suggest,  reading from the database and then using the result in SQLExecutor query requires a datetime string format change.  To do this you can use the format string %Y-%m-%d %H:%M:%S

  • DateTimeConverter transformer. If you use this a lot, then perhaps use Presets to save the default or a named preset
  • In Text Editor (AttributeCreator,  AttributeManager etc.) use the @DateTimeFormat(<attr>,%Y-%m-%d %H:%M:%S)
  • Directly in your SQL, you can use @DateTimeFormat(<attr>,%Y-%m-%d %H:%M:%S)
SELECT * FROM dbo.[CellSignalsTable]
where [recorded_tstamp] = '@DateTimeFormat(@Value(recorded_tstamp),%Y-%m-%d %H:%M:%S)';

 

M
Badge +2

example workspace attached just for completeness (FME 2021)

A
Badge

@agelfert​ Thanks for sharing your frustration.  Here's the summary of how FME is behaving:

  • FME readers (including those in FeatureReader) will return the standard FME datetime format (i.e.20111202220144) 
  • A SELECT in SQLExecutor or SQLCreator that returns a datetime data type will return the standard FME datetime format (not the database datetime string).
  • Any other SQL query (in SQLexecutor/Creator, SQL To Run After Write etc.) that references a datetime field will have to use the databases preferred datetime string format (i.e. 2011-12-02 22:01:44.000 for SQL Server, PostGIS, Oracle). 

So as you suggest,  reading from the database and then using the result in SQLExecutor query requires a datetime string format change.  To do this you can use the format string %Y-%m-%d %H:%M:%S

  • DateTimeConverter transformer. If you use this a lot, then perhaps use Presets to save the default or a named preset
  • In Text Editor (AttributeCreator,  AttributeManager etc.) use the @DateTimeFormat(<attr>,%Y-%m-%d %H:%M:%S)
  • Directly in your SQL, you can use @DateTimeFormat(<attr>,%Y-%m-%d %H:%M:%S)
SELECT * FROM dbo.[CellSignalsTable]
where [recorded_tstamp] = '@DateTimeFormat(@Value(recorded_tstamp),%Y-%m-%d %H:%M:%S)';

 

@Mark Stoakes​ Mark, you rock! Thanks for putting it so "succinctly"! I think I had the first part of your response figured out but you explained why. I will be sure to use the alternate SQL to replace what I was trying to use inside the SQL Executor. Not to be asking too much but - being such a great explainer - would you also take a look at my (tangentially) related and likely similarly rudimentary question about NULLs for which I spawned this separate thread? 😀 

kimo
Badge +1

It is not just SQLExecutor that has the NULL datetime problem. I cannot seem to write a NULL date into the OGCgeopackage writer to a field that has a datetime type from a CSV source either. My workaround is to trap the NULLS in the DateTimeConverter and use the NullAttributeMapper to map it again!

Reply


Community Stats

28,711
Posts
109,488
Replies
36,560
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.