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 convert 3 integer columns to date?

oscarlim
6 - Meteoroid

Hello.  My data set has 3 columns for Day, Month and Year.  All are in integer. 

I want to convert the 3 into a date field. 

For example, Day = 3, Month = 6, Year = 2017

Result is a date field: 2017-06-03

 

Using Select tool, I changed the data type from integer to V_String.  But then don't know how to go further.

I was looking for a concatenate string function but could not find it.  

 

Thanks for your help. 

Oscar

 

4 REPLIES 4
JoshKushner
12 - Quasar
You can use the formula:
DateTimeParse([year]+"-"+[month]+"-"+[day],"%Y-%m-%d")

Make sure month and day are all two digits (ex. 01 for January ) and make sure the formula outputs a date format

%Y is used for a 4 digit year (ex.2017)
%y is ised for a 2 digit year (ex. 17)
oscarlim
6 - Meteoroid

Thanks Josh. The formula worked. 

 

I used the formula as you suggested and the date resulted in leading 0's.  

 

After I used the Select tool to change from integer to V_String, how do I add a leading 0, if applicable?

 

Thanks.

Oscar

jdunkerley79
ACE Emeritus
ACE Emeritus

If you want to start with numbers:

 

ToString([Year])+"-"+ RIGHT("0"+ToString([Month]),2)+"-"+ RIGHT("0"+ToString([Day]),2)

should work

 

(or if you have the Abacus functions MakeDate(Year, Month, Day))

oscarlim
6 - Meteoroid

Thanks. The ToString worked as well.

Labels