Question

Using DateTime function in SQL Server Writer for dates from CSV

tim_wood
Rank
Badge +7

FME 2017.0 reading CSV data including dates in the format 2009-04-23 with no quotes around them.

I'd like to write this straight to an SQL Server 2012 table so I've connected the Reader straight to the Writer. However it won't write the date values from the CSV into the SQL Server table as a date field.

So I thought I'd take advantage of the new DateTime functions to save having to stick a DateFormatter in the middle. So I've been trying things like @DateTimeFormat(@Value(col4),%Y%m%d) and @DateTimeFormat(@Value(col4),%Y"-"%m"-"%d) in the Value column under User Attributes in the Writer properties. But I can't seem to find anything that works. I've tried @DateTimeParse, @DateTimeCast, @DateTimeFormat but the Translation Log keeps throwing up warnings e.g. 'Failed to evaluate expression '@DateTimeParse(2008-02-29,%Y%m%d,repair)'. Result is set to null.'

Is this actually possible or do I need to go back to using the DateFormatter?

I want to output the date values as this data type:

https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql

10 replies

itay
Rank
Badge +16

you need to add the - symbol to the formatting

tim_wood
Rank
Badge +7

you need to add the - symbol to the formatting

I've tried @DateTimeFormat(@Value(col4),%Y"-"%m"-"%d) and @DateTimeFormat(@Value(col4),%Y-%m-%d)

 

 

tim_wood
Rank
Badge +7

OK I think @DateTimeParse(@Value(col4),%Y-%m-%d,repair) might work as I only seem to be getting warnings for empty string values i.e. 'Failed to evaluate expression '@DateTimeParse(%Y-%m-%d,repair)'. Result is set to null.'

I wanted to ask the question before I got too far down the road of trying lots of permutations without being sure it would actually do what I want...

itay
Rank
Badge +16

OK I think @DateTimeParse(@Value(col4),%Y-%m-%d,repair) might work as I only seem to be getting warnings for empty string values i.e. 'Failed to evaluate expression '@DateTimeParse(%Y-%m-%d,repair)'. Result is set to null.'

I wanted to ask the question before I got too far down the road of trying lots of permutations without being sure it would actually do what I want...

Always a good practice is to map null values before formatting, the dateformatter actually have a setting for dealing with null values
L
Badge

Hi @tim_wood

 

you are right, you need to use @DateTimeParse() to parse (interpret) your source datetime values. @DateTimeParse() will convert you source 2009-04-23 into 20090423 i.e. datetime in fme format which will be handled successfully by the Writer.

FME 2017.1 now has DateTimeFormatter - in case you prefer transformer over function.

takashi
Rank
Userlevel 2
Badge +17

OK I think @DateTimeParse(@Value(col4),%Y-%m-%d,repair) might work as I only seem to be getting warnings for empty string values i.e. 'Failed to evaluate expression '@DateTimeParse(%Y-%m-%d,repair)'. Result is set to null.'

I wanted to ask the question before I got too far down the road of trying lots of permutations without being sure it would actually do what I want...

You don't need to go a long way around, can always refer to the documentation.

 

FME Workbench Help > Search | Date/Time Functions

 

 

tim_wood
Rank
Badge +7

OK I think @DateTimeParse(@Value(col4),%Y-%m-%d,repair) might work as I only seem to be getting warnings for empty string values i.e. 'Failed to evaluate expression '@DateTimeParse(%Y-%m-%d,repair)'. Result is set to null.'

I wanted to ask the question before I got too far down the road of trying lots of permutations without being sure it would actually do what I want...

@itay I'll make the Value setting in the Writer properties a Conditional Value then I can test for Empty String and set to null that way. I get the same result from @DateTimeParse but in a less elegant way and with a load of messy warning messages in the Translation Log.

 

 

@takashi I was using the documentation in conjunction with the SQL Server help but wasn't sure what I needed to do to format my values to for SQL Server to accept them. For example, using @DateTimeFormat(@Value(col4),%Y"-"%m"-"%d) with or without quotes around the hyphens doesn't seem to work, even though the output format should be what SQL Server wants.

 

https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_Workbench/!Transformer_Parameters/Date-Time-Functions.htm

 

https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql

 

 

In some ways I'm not sure why I need to format the date at all since the input value matches the SQL Server format YYYY-MM-DD. But I guess it's a similar thing to the difference between null, missing, empty string, zero, or maybe I need a bit more SQL Server training!

 

 

Thanks all for your feedback.
tim_wood
Rank
Badge +7

Oh and I could be wrong but I think putting a transformer in the middle of the process might be slower, particularly if the feature table needs to be split. See...

https://knowledge.safe.com/questions/43059/what-does-this-mean-splitting-feature-table-into-f.html

takashi
Rank
Userlevel 2
Badge +17
@itay I'll make the Value setting in the Writer properties a Conditional Value then I can test for Empty String and set to null that way. I get the same result from @DateTimeParse but in a less elegant way and with a load of messy warning messages in the Translation Log.

 

 

@takashi I was using the documentation in conjunction with the SQL Server help but wasn't sure what I needed to do to format my values to for SQL Server to accept them. For example, using @DateTimeFormat(@Value(col4),%Y"-"%m"-"%d) with or without quotes around the hyphens doesn't seem to work, even though the output format should be what SQL Server wants.

 

https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_Workbench/!Transformer_Parameters/Date-Time-Functions.htm

 

https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql

 

 

In some ways I'm not sure why I need to format the date at all since the input value matches the SQL Server format YYYY-MM-DD. But I guess it's a similar thing to the difference between null, missing, empty string, zero, or maybe I need a bit more SQL Server training!

 

 

Thanks all for your feedback.
Generally FME Writers automatically convert the FME Standard date format (%Y%m%d) to the destination format specific date format (e.g. %Y-%m-%d for MS SQL Server). You don't need to care of the MS SQL Server specific date format, unless you are going to insert records into the table with SQL statements.

 

tim_wood
Rank
Badge +7

Oh and I could be wrong but I think putting a transformer in the middle of the process might be slower, particularly if the feature table needs to be split. See...

https://knowledge.safe.com/questions/43059/what-does-this-mean-splitting-feature-table-into-f.html

Well there you go... For 1 million records, Reader > AttributeManager (to rename columns) > DateFormatter > Writer takes 45 minutes. Reader > Writer with date formatting in the Writer takes 1 hour.

 

 

Reply


Community Stats

28,707
Posts
109,479
Replies
36,553
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.