How to Use Date Convert across 20th and 21st Century

[pennylaf]

Hello!  I have a monthly data set that starts in Jan-92 and goes through Dec-16.  The data is coming in as a V-String and I am converting to a date by using DateTimeParse "%b-%y" and then DateTimeTrim 'lastofmonth' to convert to a Month End Date.  All my dates in the 1990's are converting as 2090's.  Any suggestions on how to make the dates look at the previous Century and not into the future?

[MarqueeCrew · Accepted solution]

@pennylaf,

Here is one way to skin that cat!

IF toNumber(RIGHT([Date],2)) < 18	THEN
	DateTimeFormat(
		DateTimeTrim(
			Datetimeformat(
				DateTimeParse(
					Replace([Date],"-","-20"),"%b-%Y"),
				"%Y-%m-%d"),
			"lastofMonth")
		,"%Y-%m-%d")
ELSE 
	DateTimeFormat(
		DateTimeTrim(
			Datetimeformat(
				DateTimeParse(
					Replace([Date],"-","-19"),"%b-%Y"),
				"%Y-%m-%d"),
			"lastofMonth")
		,"%Y-%m-%d")
ENDIF

Cheers,

Mark

[pennylaf]

Beautiful!  Thank you!

[alex]

Different solution if your data will never start prior to the 1992 date.