Skip to main content
Solved

DateTimeParse - why does this not work?

  • January 17, 2020
  • 10 replies
  • 578 views
tim_wood
Contributor
Forum|alt.badge.img+8

The DateTimeParse function is driving me mad (in FME 2018.1)!

What is the problem with this...?

Failed to evaluate expression '@DateTimeParse(3-Jun-2020,%Y%m%d,repair)'. Result is set to null

I have also tried @DateTimeFormat and @DateTimeCast and things like putting quotes around the input etc. Nothing works.

I want to use the function because I want to convert a value of "3 June 2020" to 2020-06-03 to write into a date field in SQL Server. I have already use the string functions to extract the substrings for day (3), 3 character month (Jun) and year (2020). I could go the long winded way and write these to separate attributes then use the DateTimeConverter transformer, but why should I?

Best answer by david_r

The specified format does not correspond to the input format. Try:

@DateTimeParse(3-Jun-2020,%d-%b-%Y,repair)

Result: 20200603

If you need a specific output format, you need to chain it with the DateTimeFormat:

@DateTimeFormat(@DateTimeParse(3-Jun-2020,%d-%b-%Y,repair),%Y-%m-%d)

Result: 2020-06-03

Also look at the documentation:

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

 

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.

10 replies

david_r
Celebrity
  • david_rCelebrity
  • 8392 replies
  • Best Answer
  • January 17, 2020

The specified format does not correspond to the input format. Try:

@DateTimeParse(3-Jun-2020,%d-%b-%Y,repair)

Result: 20200603

If you need a specific output format, you need to chain it with the DateTimeFormat:

@DateTimeFormat(@DateTimeParse(3-Jun-2020,%d-%b-%Y,repair),%Y-%m-%d)

Result: 2020-06-03

Also look at the documentation:

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

 

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • 311 replies
  • January 17, 2020

For info, DateTimeConverter works (see below). So how do I do this using function(s) in the Text Editor?

david_r
Celebrity
  • david_rCelebrity
  • 8392 replies
  • January 17, 2020

For info, DateTimeConverter works (see below). So how do I do this using function(s) in the Text Editor?

This is because the DateTimeConverter does two things: first a @DateTimeParse (input format), then a @DateTimeFormat (output format).

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • 311 replies
  • January 17, 2020

The specified format does not correspond to the input format. Try:

@DateTimeParse(3-Jun-2020,%d-%b-%Y,repair)

Result: 20200603

If you need a specific output format, you need to chain it with the DateTimeFormat:

@DateTimeFormat(@DateTimeParse(3-Jun-2020,%d-%b-%Y,repair),%Y-%m-%d)

Result: 2020-06-03

Also look at the documentation:

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

 

Hi @david_r I have been looking at the documentation. The examples are pretty basic and I couldn't see anything that tells you how to parse/format AND convert to a different date format. But I tried your suggestion in the comment above and that works, so thank you :-) 

 

 

Here's the whole thing for anyone that's interested:

 

@DateTimeFormat(@DateTimeParse(@Substring(@Value(Col2),0,@FindString(@Value(Col2)," "))-@Substring(@Value(Col2),@FindString(@Value(Col2)," ")+1,3)-@Right(@Value(Col2),4),%d-%b-%Y,repair),%Y%m%d)
takashi
Celebrity
  • takashiCelebrity
  • 7843 replies
  • January 18, 2020

Hi @david_r I have been looking at the documentation. The examples are pretty basic and I couldn't see anything that tells you how to parse/format AND convert to a different date format. But I tried your suggestion in the comment above and that works, so thank you :-) 

 

 

Here's the whole thing for anyone that's interested:

 

@DateTimeFormat(@DateTimeParse(@Substring(@Value(Col2),0,@FindString(@Value(Col2)," "))-@Substring(@Value(Col2),@FindString(@Value(Col2)," ")+1,3)-@Right(@Value(Col2),4),%d-%b-%Y,repair),%Y%m%d)

If you intend to convert "3 Jun 2020" to "20200603" for example, the expression you quoted is a bit redundant, this expression should work as well.

@DateTimeFormat(@DateTimeParse(@Value(Col2),%d %b %Y,repair),%Y%m%d)
Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • 311 replies
  • January 20, 2020

If you intend to convert "3 Jun 2020" to "20200603" for example, the expression you quoted is a bit redundant, this expression should work as well.

@DateTimeFormat(@DateTimeParse(@Value(Col2),%d %b %Y,repair),%Y%m%d)

Will that work on full month names? Maybe the "3 JunE 2020" example wasn't the best one. My source date values are like this:

4 January 2020

15 February 2020

I have assumed that I need to trim the month text down to 3 characters before doing anything else.

david_r
Celebrity
  • david_rCelebrity
  • 8392 replies
  • January 20, 2020

Will that work on full month names? Maybe the "3 JunE 2020" example wasn't the best one. My source date values are like this:

4 January 2020

15 February 2020

I have assumed that I need to trim the month text down to 3 characters before doing anything else.

As per the documentation, %b corresponds to the abbreviated, 3-letter English month name.

For the full (not abbreviated) English month name, use %B in stead (upper case).

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • 311 replies
  • January 20, 2020

As per the documentation, %b corresponds to the abbreviated, 3-letter English month name. 

For the full (not abbreviated) English month name, use %B in stead (upper case).

Ah!  I was looking for that in the documentation but obviously missed it.  So I should be able to simplify my code to what Takashi suggests with that amendment...

@DateTimeFormat(@DateTimeParse(@Value(Col2),%d %B %Y,repair),%Y%m%d)
david_r
Celebrity
  • david_rCelebrity
  • 8392 replies
  • January 20, 2020

Ah!  I was looking for that in the documentation but obviously missed it.  So I should be able to simplify my code to what Takashi suggests with that amendment...

@DateTimeFormat(@DateTimeParse(@Value(Col2),%d %B %Y,repair),%Y%m%d)

Or even shorter, based on my first reply:

@DateTimeParse(@Value(Col2),%d %B %Y,repair)

DateTimeParse will return the FME datetime format, meaning that you don't have to specifically format it as such.

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • 311 replies
  • January 20, 2020

Or even shorter, based on my first reply:

@DateTimeParse(@Value(Col2),%d %B %Y,repair)

DateTimeParse will return the FME datetime format, meaning that you don't have to specifically format it as such.

Works a treat!  You guys are the best :-)

Community Stats

32,078
Posts
121,802
Replies
39,709
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.