How to format date as 2023/04/20



Some data source have simple single date format, not multiple date format. I would use FME dateTimeConverter to do this simple job.





However, some source data have multiple date format in same column, FME dateTimeConverter would not work, it only work with single format. See below example with multiple date format in one column




FME auto detect does not work, it only work if date is already in ISO-date or FME default date format, other than that, it failed.



Use regex to filter out different format of date 

 test regex here :   or

JavaScript Regex Cheatsheet is here


 1) find 2023-10-04   use regex \d{4}-\d{2}-\d{2}

       FME function use   @DateTimeParse(@Value(Src_Date), %Y-%m-%d)



2) find  31/04/2023 or 31/4/2023     

use regex     (0?[1-9]|[12][0-9]|3[01])/(0?[1-9]|1[012])/\d{4} 

FME function use

 @DateTimeParse(@Value(Src_Date), %d/%m/%Y) (1 digital month ok,if with delimit)









 3)   find  4/3/2020 or  04/03/2020     

 use regex    ^(0?[1-9]|1[012])\/(0?[1-9]|[12][0-9]|3[01])\/\d{4}

            FME function use 

           @DateTimeParse(@Value(Src_Date), %m/%d/%Y) (1 digital month ok,if with delimit)

           @DateTimeParse(@Value(Src_Date), %N/%e/%Y)   (I am not use here)   

FME document is here!Transformer_Parameters/Date-Time-Functions.htm






  4)    find  2021/11/08    or   2021/1/8 

use regex   \d{4}\/(0?[1-9]|1[012])\/(0?[1-9]|[12][0-9]|3[01]) 

            FME function use 

            @DateTimeParse(@Value(Src_Date), %Y/%m/%d)





5) find 2023     year only 

           use regex   ^\d{4}$ 

        FME function use   @DateTimeParse(@Value(Src_Date), %Y) will fail, not sure why, should be FME bugs not fixed.

        alternatively, FME default format is 20230101 without slash, or any delimit

       so I use   @Value(Src_Date)0101   

 Never use   @Value(Src_Date)0000   (Warning:  20230000 is not valid date will fail your DateTimeConvertor)





Final regex table




Please log in or register