Format date FME (part 1) Regex filter

Question

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

 

https://transparentgov.net:3200/googlemaps141/default?layer_id=0&layer=NYPAD2_Easement&center_lat=41.18333803421051&center_long=-73.7791579159306&center_zoom=13&url=https%3A%2F%2Fservices6.arcgis.com%2FDZHaqZm9cxOD4CWM%2FArcGIS%2Frest%2Fservices%2FNYPAD2_Easement%2FFeatureServer&panto=0&overlayType=overlayType_none&select_field=14&select_fieldvalue=-1&distinctorgroupby=groupby

 

 

https://transparentgov.net:3200/googlemaps141/default?layer_id=0&layer=NYPAD_2_Fee&center_lat=43.07607440739406&center_long=-74.21026135147456&center_zoom=19&url=https%3A%2F%2Fservices6.arcgis.com%2FDZHaqZm9cxOD4CWM%2FArcGIS%2Frest%2Fservices%2FNYPAD_2_Fee%2FFeatureServer&panto=0&overlayType=overlayType_none&select_field=14&select_fieldvalue=-1&distinctorgroupby=groupby

 

 

 

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 :   https://www.debuggex.com/   or      https://regex101.com

JavaScript Regex Cheatsheet is here

https://www.debuggex.com/cheatsheet/regex/javascript

 

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

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

https://regex101.com/r/ETRxIg/2

 

https://www.debuggex.com/r/pF2sOvYyZldizz3Z

 

 

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)

https://regex101.com/r/ETRxIg/1

 

https://www.debuggex.com/r/OGAZSl0I6qsz1992

 

 

 

 

 

 

 

 

 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  https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_Transformers/!Transformer_Parameters/Date-Time-Functions.htm

 

https://regex101.com/r/PTGoVi/2

 

 

https://www.debuggex.com/r/enm2dZa4eO5ql4es

 

 

 

 

 

  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)

 

https://regex101.com/r/IJALbe/1

 

 

 

https://www.debuggex.com/r/28Z918C_CsmfI6KK

 

 

 

 

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)

 

 

https://regex101.com/r/PseMzJ/1

 

https://www.debuggex.com/r/t3Zch5QfIDrWnRnf

 

 

 

 

Final regex table