• EN
Solved

DateTimeParse - why does this not work?

tim_wood
Rank
Badge +7

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?

icon

Best answer by david_r 17 January 2020, 12:07

View original

10 replies

david_r
Rank
Userlevel 5

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
Rank
Badge +7

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

david_r
Rank
Userlevel 5

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
Rank
Badge +7

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
Rank
Userlevel 3
Badge +17

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)
tim_wood
Rank
Badge +7

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
Rank
Userlevel 5

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
Rank
Badge +7

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
Rank
Userlevel 5

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
Rank
Badge +7

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 :-)

Reply


Community Stats

28,802
Posts
109,791
Replies
36,632
Members
Terms & ConditionsCookie 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.