Skip to main content
Solved

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

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.

View original
Did this help you find an answer to your question?

7 replies

fmelizard
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Contributor
  • 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+17
  • 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
fmelizard wrote:

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
Influencer
  • takashiInfluencer
  • July 27, 2017
rrdlpl wrote:
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.

 

R
Forum|alt.badge.img
  • rrdlpl
  • Author
  • July 27, 2017
takashi wrote:
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
Influencer
  • takashiInfluencer
  • July 27, 2017
rrdlpl wrote:
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.

 

 

Reply


Helpful Members This Week

Community Stats

31,135
Posts
118,045
Replies
38,877
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.