For some reason, I can't read in a date such as
02-JUL-08
using
%d-%b-%y
It says that %y is output only.
Surely there must be a way to read two digit years.
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.
+21- Safer
- 648 replies
Hi @dta255
Unfortunately, you've run into a known issue where DateTimeParse function and the DateTimeConverter transformer is unable to read in two digit years. Attached is a workspace that will convert two-digit year values to four-digit years by prefixing '20'. If you have any year values earlier than 2000, you will have to make edits the workspace.
I hope this helps.
- 7843 replies
Hi @dta255, the format specifier %y (two-digit year) is not supported to parse a date/time string. See also here: Date/Time Functions
If you are sure that the date always belongs to the 21st century, simply replace the last two digits (xx) in the date string with '20xx', then parse the resulting date string with %d-%b-%Y.
@DateTimeParse(@ReplaceRegEx(02-JUL-08,"^(.+)(..)$",\120\2),%d-%b-%Y)- 7843 replies
Hi @dta255, the format specifier %y (two-digit year) is not supported to parse a date/time string. See also here: Date/Time Functions
If you are sure that the date always belongs to the 21st century, simply replace the last two digits (xx) in the date string with '20xx', then parse the resulting date string with %d-%b-%Y.
@DateTimeParse(@ReplaceRegEx(02-JUL-08,"^(.+)(..)$",\120\2),%d-%b-%Y)
Hi @dta255
Unfortunately, you've run into a known issue where DateTimeParse function and the DateTimeConverter transformer is unable to read in two digit years. Attached is a workspace that will convert two-digit year values to four-digit years by prefixing '20'. If you have any year values earlier than 2000, you will have to make edits the workspace.
I hope this helps.
Good morning
I came across this post as I am also experiencing issues when trying to parse dates in either of these formats e.g. dd/mm/yy and dd.mm.yy. However the formatting of these values is also not consistent, in that there are values that could be dd/m/yy, d/mm/yy, dd.m.yy or d.mm.yy. I tried to download the sample workbench you had provided but was unable to access. I don't suppose you still have a copy that you can share? Many thanks.
+23- Contributor
- 571 replies
Good morning
I came across this post as I am also experiencing issues when trying to parse dates in either of these formats e.g. dd/mm/yy and dd.mm.yy. However the formatting of these values is also not consistent, in that there are values that could be dd/m/yy, d/mm/yy, dd.m.yy or d.mm.yy. I tried to download the sample workbench you had provided but was unable to access. I don't suppose you still have a copy that you can share? Many thanks.
For that type of inconsistency use String Replacer "/"->"-" and another one "."->"-".
Then, apart from the two suggestions here you can try AttributeSplitter with delimiter "-" followed by StringFormatter set to format day and month to 02d and pad year to the left with 19 or 20 (you should split the flow in two: one for 1900 and one for 2000). And just concatenate them to a valid date.
Good morning
I came across this post as I am also experiencing issues when trying to parse dates in either of these formats e.g. dd/mm/yy and dd.mm.yy. However the formatting of these values is also not consistent, in that there are values that could be dd/m/yy, d/mm/yy, dd.m.yy or d.mm.yy. I tried to download the sample workbench you had provided but was unable to access. I don't suppose you still have a copy that you can share? Many thanks.
@caracadrian thanks for your assistance, and I have managed to get up to the StringFormatter. However, as I have not used this transformer before I'm not 100% clear on what syntax I would need to enter to format the string as you have suggested. If you could provide that it would be very much appreciated. I am also unclear on the output of using this translator i.e. once you have formatted a list (in this case) where does your formatted string end up i.e. a new output field etc. Many thanks
+23- Contributor
- 571 replies
Good morning
I came across this post as I am also experiencing issues when trying to parse dates in either of these formats e.g. dd/mm/yy and dd.mm.yy. However the formatting of these values is also not consistent, in that there are values that could be dd/m/yy, d/mm/yy, dd.m.yy or d.mm.yy. I tried to download the sample workbench you had provided but was unable to access. I don't suppose you still have a copy that you can share? Many thanks.
@gis_person sorry for the late reply.
Any problem has multiple solutions. I have attached a workspace that converts your date examples in ISO dates.
Best of luck.