Skip to main content
Solved

How can I cast a string to datetime for MS SQL?

  • July 26, 2017
  • 7 replies
  • 80 views
R
Forum|alt.badge.img

Hi,

In my workspace I'm inserting through a FeatureWriter into MS SQL Table and I'm trying to cast a string to a datetime, but it doesn't seem to work.

This is the format that I'm reading: dd/mm/YYYY and I'm trying to parse the attribute column in FME as this:

@DateTimeParse(@Value(DATE_OF_BIRTH),%d/%m/%Y)

But I'm getting this error from the console:

2017-07-26 15:25:28| 162.1| 0.0|WARN |Table1_StudentUpdate_FeatureWriter_f2afd4cb_4a56_4aec_8f42_9e384693c40417: Failed to evaluate expression '@DateTimeParse(03/13/2010,%d/%m/%Y)'. Result is set to null.

Best answer by fmelizard

Hi @rrdlpl, it seems like the date in your log line is in the format MM/DD/YYYY. It's likely you're getting a null value in your output because the day and month are switched. Should the 13th month be an error, you can use the repair function of DateTimeParse.

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

7 replies

fmelizard
Safer
Forum|alt.badge.img+21
  • fmelizardSafer
  • Safer
  • Best Answer
  • July 26, 2017

Hi @rrdlpl, it seems like the date in your log line is in the format MM/DD/YYYY. It's likely you're getting a null value in your output because the day and month are switched. Should the 13th month be an error, you can use the repair function of DateTimeParse.

erik_jan
Contributor
Forum|alt.badge.img+23
  • erik_janContributor
  • Contributor
  • July 26, 2017

Could it be that both the date string and the format string need to be enclosed in quotes?

@DateTimeParse('03/13/2010','%m/%d/%Y')

R
Forum|alt.badge.img
  • rrdlpl
  • Author
  • July 27, 2017

Hi @rrdlpl, it seems like the date in your log line is in the format MM/DD/YYYY. It's likely you're getting a null value in your output because the day and month are switched. Should the 13th month be an error, you can use the repair function of DateTimeParse.

You're right. I switched them over, but now I'm getting this error:

 

MS SQL Server (JDBC) Writer: Error writing feature. Error: Attribute 'DATE_OF_BIRTH' does not contain a valid timestamp

 

 

lars_de_vries
Forum|alt.badge.img+10

Looking at this question on stackoverflow it seems that you should remove the slashes from the date.

takashi
Celebrity
  • takashiCelebrity
  • July 27, 2017
You're right. I switched them over, but now I'm getting this error:

 

MS SQL Server (JDBC) Writer: Error writing feature. Error: Attribute 'DATE_OF_BIRTH' does not contain a valid timestamp

 

 

Does the "DATE_OF_BIRTH" attribute contain a valid value? Check Workbench Menu: Writers > Redirect to FME Data Inspector then run the workspace, and see what value the attribute contain.

 

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
R
Forum|alt.badge.img
  • rrdlpl
  • Author
  • July 27, 2017
Does the "DATE_OF_BIRTH" attribute contain a valid value? Check Workbench Menu: Writers > Redirect to FME Data Inspector then run the workspace, and see what value the attribute contain.

 

hi @takashi, this is the format of the incoming date 05/22/2000 and I need to convert it to 2000-05-22 00:00:00.000 so it can insert into the table

 

 

takashi
Celebrity
  • takashiCelebrity
  • July 27, 2017
You're right. I switched them over, but now I'm getting this error:

 

MS SQL Server (JDBC) Writer: Error writing feature. Error: Attribute 'DATE_OF_BIRTH' does not contain a valid timestamp

 

 

The @DateTimeParse function returns a date/time string with the Standard FME Date/Time Format (typically %Y%m%d, %Y%m%d%H%M%S), and it can be written directly into a date type field of a writer feature type in general.

 

Anyway, first of all, check what date/time string caused the error.

 

 

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?

Community Stats

32,154
Posts
122,047
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.