Date Transformation

[barbaracnx]

How can I change a month level date into a day level date?

For example right now my dates are 1/1/20, 2/1/20

But instead I want every day of the months listed 1/1/20, 1/2/20, 1/3/20, etc for the whole year

[NicholasM]

Hey @barbaracnx 

I would recommend taking a look at the Generate Rows tool. Utilizing the DATETIMEADD() function

DATETIMEADD([day_column], 1, 'days')

Here is some help documentation on the Generate Rows Tool. Let me know if you have any questions

[Ladarthure]

hi @barbaracnx,

if it's to generate the dates, refer to @NicholasM post, if it's about the formating, you have to use 2 functions, datetimeparse and then datetimeformat to get the format you want (find a sample workflow attached).

[barbaracnx]

That for some reason isn't returning anything... am I doing something wrong.

To clarify I want to change 1/1/20 into 1/1/20, 1/2/20, 1/3/20, 1/4/20, etc

[VianneyM]

Hi @barbaracnx,

now I understand what you want to achieve, in this case you want to create records.

for that you want to make sure you have a date data type (use the datetime format tool in case your date column is not yet with that data type)  

Use the formula tool to calculate the end of the month for each row

then use the generate rows tool where you iníciate the value with the first record of the date column and you increase the value by one day until the date is smaller or equals to las day of month column 

have a look into this example. This shows you the configuration of the generate rows tool 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Generate-Date-values/td-p/233

Let me know if you have questions

best,

vianney

[Ladarthure · Accepted solution]

here is a sample workflow with the combination of both generate row and the date formating, you first transform the dates into dateformats with a formula, then you use another formula (datetimetrim) to get the first and last of the month and finaly you can use a generate rows to have all the rows for all the possible days in the month.

Hope it helped!

[barbaracnx]

Perfect! Thank you!

[OliveiraLaysa]

Hello, @barbaracnx.

To change the date format you can also use the tool Convert Date. It's pretty much easy.

First you just have to input the tool and choose the format to convert. If i's String to Date or Date to String.

Than you select the field you want to convert and specify the name of the new column that will be created with the new data.

Once you've done that, it's time for you to choose the format you want to use:

The tool will create a new column with the information you created.

Hope it helps!!