Question

AttributeManager reading in a two digit year

D

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.

7 replies

debbiatsafe
Rank
Userlevel 3
Badge +17

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.

2digityear-to-4digityear.fmw

takashi
Rank
Userlevel 2
Badge +17

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)
takashi
Rank
Userlevel 2
Badge +17

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)
Ah, I overlooked @DebbiAtSafe's excellent suggestion. If the date could belong to either the 20th or 21st century, you have to determine that with some way.

 

gis_person

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.

2digityear-to-4digityear.fmw

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
Rank
Badge +20

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.

gis_person

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

caracadrian
Rank
Badge +20

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.

Reply


Community Stats

28,721
Posts
109,501
Replies
36,560
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.