Trim Month from YYYY-MM-DD and return YYYY-MM where data type = Date

[dlopez]

Hi Community, 

sounds silly I know, but I've been browsing the community and can't seem to find a solution. I'm clear on that you can take a date and parse out the Month and Year, but every time it returns a string value and not a date value. Would be great to return a Month and Year that is a date value, any suggestions/thoughts would be greatly appreciated. 

Input: YYYY-MM-DD  Data Type = Date

Desired Output YYYY-MM Date Type = Date

What I'm seeing a lot of responses provide YYYY-MM Date Type = V_String

Thanks again!

[JosephSerpis]

Hi @dlopez what you are asking is not possible YYYY-MM-DD is the only recognised format for a Date data Type in Alteryx. YYYY-MM will have to be a string field data type according to Alteryx. The rationale behind this is so that the other date functions like datetimeadd/datetimediff will have a consistent format.

[dlopez]

Hi @JosephSerpis 

That's what I was thinking, ok solution thought 2. Is it possible to "relabel" all date fields to the first of the month based off of the date field. 

Example: 

Input Date	Desired Output Date
2020-05-21	2020-05-01
2020-05-15	2020-05-01
2020-03-08	2020-03-01

Thanks for your help, greatly appreciated. 

[JosephSerpis · Accepted solution]

Hi @dlopez yes it is possible. Have attached a workflow which showcases how to do that.

[christine_assaad]

Hi @dlopez 

This is another way to achieve desired results

[Qiu]

@christine_assaad 
DateTimeTrim is nice one!

[Qiu]

The power of formula.
Tried something different.

[estherb47]

So many ways to solve this @dlopez 

Give this one a try and let me know what you think. I'm pulling out the year and month, formatting with the separating dashes and adding 01 at the end. Then use Date Time Parse to put it into date format. This is the process I'm often using at work, though often doing two days per month (the 1st and the 15th)

Cheers! 

Esther