Question
Dates before 1902
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.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
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