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?
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
For info, DateTimeConverter works (see below). So how do I do this using function(s) in the Text Editor?
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).
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)
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)
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.
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).
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)
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.
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 :-)
