How to add new column with DateTimeAdd 'fixed date' to existing YYYY-MM-DD column?

[CoreyS]

Hello Alteryx Community,

I need your help!

I'm trying to add a new column to my dataset using the Formula tool, while modifying the existing date format (yyyy-mm-dd) via DateTimeAdd function to set a fixed date for every date listed. 

For instance...here's what I'm attempting to accomplish with no luck at the moment.

Input: Example

[Birthday] = 1966-01-26 (yyyy-mm-dd)

[Birthday] = 1960-11-19 (yyyy-mm-dd)

Output (desired): Add 'fixed date' to every [Birthday] entry = yyyy-07-01

[Birthday] = 1966-07-01 (yyyy-mm-dd)

[Birthday] = 1960-07-01 (yyyy-mm-dd)

What I know

I know that if I use the Formula tool, I can calculate [Age] by using the following:

DateTimeDiff(DateTimeToday(),[Birthday],"Years")

What I don't know

I don't know how to use the Formula tool to add 'fixed date' to every existing [Birthday] entry in order for me to calculate the [Age].

By the way, I'm not emotionally attached to this approach so if there's a better way to do it, I'm open to change my approach.

Thanks for your continued support in advance!

Respectfully,

Corey

[danilang · Accepted solution]

Hi @CoreyS 

You can string functions to generate dates, as in 

Substring([dob],0,4)+"-07-01"

to give you 1966-07-01

Dan

[CoreyS]

Hi @danilang 

Thanks for your help! 

I used the 'Substring' function your recommended to generate dates and it worked perfectly. 

Your support is greatly appreciated!

Respectfully,

Corey