Skip to main content
I have  an excel spreadsheet with dates from year 1892- present. The date formats are either YYYY-mm-dd OR mm/dd/YYYY. I am trying to convert all dates to format %Y-%m-%d using the data formatter and get the following result YYYYmmdd000000.000. My goal is to convert the dates to %Y-%m-%d format and write it into a geodatabase. Please help. 
I also tried using DataConverter and getting 1900-01-01 as the result for dates of all formats and also Null value dates. 
Hi,

 

 

unfortunately the DateFormatter isn't the most robust transformer. There is a DateConverter in the FME Store that is better, but it is based on a Python module which, for some reason, isn't very good with dates < 1900.

 

 

The easiest solution is perhaps to just check for the presence of a slash "/" (StringSearcher) and then use a couple of SubstringExtractors and StringConcatenators to re-structure those.

 

 

David
Here is a script that you can use in a PythonCaller:

 

 

import fmeobjects import time   def FeatureProcessor(feature):     dt = feature.getAttribute("input_date")     try:         t = time.strptime(dt, "%m/%d/%Y")     except:         t = time.strptime(dt, "%Y-%m-%d")     result = "%04d%02d%02d" % (t.tm_year, t.tm_mon, t.tm_mday)     feature.setAttribute("formatted_date", result)   It expects an attribute "input_date" that is either on the form MM/DD/YYYY or YYYY-MM-DD. It will output an attribute "formatted_date" on the format YYYYMMDD.

 

 

Hope this helps.

 

 

David

Reply