Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 4 down vote favorite

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
share|improve this question

2 Answers

up vote 6 down vote accepted

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

share|improve this answer
Thanks Mark. I ended up using a StringSearcher and a regex to get the date out of the filename, then in a StringConcatenator I rearranged the _matched_parts to YYYYMMDD. This then inserts into the datetime SQL Server field just fine. – Chad Cooper Nov 9 '10 at 19:48
up vote 1 down vote

Creator> TimeStamper> [^d-^b-^y] > SQLExecutor [DBMS settings] should work

http://groups.google.com/group/fmetalk/browse_thread/thread/24b057b19e55fad9/ded0a9d2baa73c81

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.