Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

pennylaf
5 - Atom

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?

3 REPLIES 3
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
pennylaf
5 - Atom

Beautiful!  Thank you!

alex
11 - Bolide

date3.JPG

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

Labels