community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

to combine month and year and include the last day of the month

Asteroid

Hi All,

I am stuck at this place . In my dataset i have 2 columns with year and month --- i need to get the date from these 2 columns.

the day could be the last day of the month .

so if month is 12, my output date will be --- 31/12/2017 and soon.

 

Please help me with it.

 

Thanks in advance

 

 

YearMonthOUTPUT
20171231/12/2017
20171130/11/2017
20171031/10/2017
Alteryx Certified Partner
Alteryx Certified Partner
Try this please:

Datetimeadd(
Datetimeadd([year]+"-"+[month]+"-01",1,"months"),-1,
"Days")

Edited

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Asteroid

HI,

this is giving me an error that argument 2 of datetime add is not a number :(

Alteryx Certified Partner
Alteryx Certified Partner
Edited. Sorry. Tired.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted

I would suggest:

DateTimeTrim(ToString([Year])+"-"+PadLeft(ToString([Month]),2,"0")+"-01","lastofmonth")

This assumes year and month are numbers as in your example. If not you can remove the ToStrings

The PadLeft will make it cope with Jan-Sep as well

 

Finally I chose to use the often overlooked datetimetrim function to get End of Month

Labels