[GIS] FME – How to convert string represenation of date to a date format that SQL Server will accept

fmetranslation

I have dates in the form of 07-28-2010 I am extracting from DWG filenames. I need to push these to a DateTime formatted table in non-spatial SQL Server 2008. SQL Server doesn't like them as strings coming in from the ETL apparently. How can I set the datatype in the ETL from string to datetime? Is this even what I need to do? I'm pretty sure this is my issue based on past experience with pushing dates to SQL Server and this error from FME:

MS SQL Server Writer: 1 attribute value(s) failed conversion, and were written as NULL values

Best Answer

From the FME Readers and Writers Manual:

When writing to the database, the writer expects the date attribute to be in the form YYYYMMDDHHMMSS.

You'll probably need to use the DateFormatter transformer to get this format. I would:

Use a StringReplacer to replace the - with / in the date attribute Use a DateFormatter set to %Y%m%d or %Y%m%d%H%M%S including time (be sure to get the case right) Write it to a datetime field in the SQL Server output

Related Question