Skip to main content
  • EN

i have issue with date

some times i have attribute has date 01.02.2010 in this format and some times the same attribute but in another excel file it shows 20100304

so when i use date converter and i set format as input %d.%m.%Y$

output %Y%m%d

 

i need output format in this format %Y%m%d because of the rest of transformer condition depends on thsi format .

but from the beginning i want to check the date ,if this attribute whatever comes,it should convert to this format %Y%m%d

i tried with tester before it ,i am not sure how to set it ,as i want to check if the date include this format %Y%m%d so pass if not ,so u have to convert whatever the format to this format as 20100304 but one of another excel files that caused me terminated ,FME reads it as 01.02.2010

and i thought ,i can get output of dateconverter when it rejects to use as input so i connected output and rejected to next transformer ,i found the date came from rejected has null value

FME version 2018

 

could someone suggest me new idea .Thanks for help

 

First of all: check in the FME Data Inspector what the values look like, never ever trust anything Excel shows you in terms of numbers or dates.

A simple test could be if the attribute contains a . it's the first format, otherwise it's the second one. Run them to two different DateTimeConverters and bring them together afterwards.

First of all: check in the FME Data Inspector what the values look like, never ever trust anything Excel shows you in terms of numbers or dates.

A simple test could be if the attribute contains a . it's the first format, otherwise it's the second one. Run them to two different DateTimeConverters and bring them together afterwards.

i was thinking about that but the problem i have many different excel fiel as input and iam afraid if i did onlytwo condition ,it will work with most of them but some not .so i want to arrange whatever the date format is to be %Y%m%d .

and it continus work .just i want to let it work with all condition not only specifin on two date format .

i checked on the inspector

some excel files have the date format as %d.%m.%Y$

and another files as %Y%m%d

but i would liek to make condition to accept any date format and convert to %Y%m%d

It is really only a variation of the solution from @redgeographics, but there are limits on what FME can auto-detect as Date formats. Either the Excel strings have to be in a recognised ISO or FME format, or you have to manually specify the formats.

See the accepted FME and ISO date/time formats here as to what can be auto-detected:

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

A solution is thus:

  1. Use AttributeCreator to try to "guess" the Date by using the formula: @DateTimeParse(@Value(DateAttributeName), FME|ISO, repair). "FME|ISO" will check if the format matches any known FME or ISO formats.
  2. In the same AttributeCreator, check for any NULLs returned in the previous line by using Conditional Values to check if the attempt to Resolve the Date resulted in NULL
  3. Try out different variations of Year, Month, Day in the @DateTimeParse function. Keep repeating this down on new lines in AttributeCreator for each different variation that exists.

Apart from that, on a more global level, try to stop your incoming data being entered into the source dataset as a Non-Standard/Non-ISO format. Date/Times are not something easy to manage without enforcement of recognised data standards.

i was thinking about that but the problem i have many different excel fiel as input and iam afraid if i did onlytwo condition ,it will work with most of them but some not .so i want to arrange whatever the date format is to be %Y%m%d .

and it continus work .just i want to let it work with all condition not only specifin on two date format .

i checked on the inspector

some excel files have the date format as %d.%m.%Y$

and another files as %Y%m%d

but i would liek to make condition to accept any date format and convert to %Y%m%d

You could set it to autodetect the format but I would not recommend that. The difference between month-first and date-first is, depending on the day of the month, undetectable.

Also, people can get very creative when it comes to writing down dates...

You could set it to autodetect the format but I would not recommend that. The difference between month-first and date-first is, depending on the day of the month, undetectable.

Also, people can get very creative when it comes to writing down dates...

thanks i will do tester for 2 option ,i did auto detect and it causes sometimes to terminate the workspace ,thanks for ur reply

It is really only a variation of the solution from @redgeographics, but there are limits on what FME can auto-detect as Date formats. Either the Excel strings have to be in a recognised ISO or FME format, or you have to manually specify the formats.

See the accepted FME and ISO date/time formats here as to what can be auto-detected:

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

A solution is thus:

  1. Use AttributeCreator to try to "guess" the Date by using the formula: @DateTimeParse(@Value(DateAttributeName), FME|ISO, repair). "FME|ISO" will check if the format matches any known FME or ISO formats.
  2. In the same AttributeCreator, check for any NULLs returned in the previous line by using Conditional Values to check if the attempt to Resolve the Date resulted in NULL
  3. Try out different variations of Year, Month, Day in the @DateTimeParse function. Keep repeating this down on new lines in AttributeCreator for each different variation that exists.

Apart from that, on a more global level, try to stop your incoming data being entered into the source dataset as a Non-Standard/Non-ISO format. Date/Times are not something easy to manage without enforcement of recognised data standards.

thanks alot i will check it

Reply


Community Stats

30,176
Posts
114,544
Replies
37,836
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.