Putting together day month and year

Hello - I have a {Month} column with numbers 1 through 12 for the month. I have been trying to make a date out of it -- meaning to add a 1 for the day and 2022 for the year. I have tried multiple different ways but keep getting conversion errors - here's the formula i have so far. Any help with this is greatly appreciated!

datetimeformat("2022-"+PadLeft(ToString([Month]),2,'0')+"-01",'%m-%d-%Y')

ERROR: ConvError: Select (274): Date: "01-01-2022 00:00:00" is not a valid DateTime

Date Time

Accepted answers

Adrian_T

Hi @J054305,

The DateTimeFormat function converts a DateTime variable into a string and not the other way around, so you should not be looking at that formula in this case.

It would also be worthwhile to keep in mind that Alteryx uses the ISO format yyyy-mm-dd HH:MM:SS to represent dates and times.

Using the ToDate() function should solve your issue here: ToDate("2022-"+PadLeft(ToString([Month]),2,'0')+"-01")

Hope this helps!

All comments

DataNath

Hey @J054305, unless I’m mistaken, the error message looks like it’s coming from a select tool, so the issue isn’t with your formula?

There’s no time element in your output so try changing it to just a Date instead of DateTime? If the issue persists then perhaps try using the DateTime tool instead of a select?

Adrian_T

Hi @J054305,

The DateTimeFormat function converts a DateTime variable into a string and not the other way around, so you should not be looking at that formula in this case.

It would also be worthwhile to keep in mind that Alteryx uses the ISO format yyyy-mm-dd HH:MM:SS to represent dates and times.

Using the ToDate() function should solve your issue here: ToDate("2022-"+PadLeft(ToString([Month]),2,'0')+"-01")

Hope this helps!

binu_acs

@J054305 One way of doing this with the DateTimeParse function

DateTimeParse('2022-'+ toString([Month])+'-01','%Y-%m-%d')

J054305

Thank you so much everyone! i was able to get a solution based on your recommendations

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703