Putting together day month and year

[J054305]

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

[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 · Accepted solution]

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!

[binuacs]

@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