Change the year component of an existing date in database

Tim McIntosh

Hi all, 
I'm trying to look for the easiest way to convert the year component of an existing date in the database. Say I have a date of "Jan 1 2000" in the database and I want it to become "Jan 1 2022", what is the best date transformation to use for this? 

Currently my thinking is the following but I feel like there is a short cut:
* Break out year from "now" by using format timestamp. 
* Break out day from the date in the database. 
* Break out month from the date in the database. 
* Parse it back into a timestamp to be written to the database. 

^^^ All the above are new variables but surely there is a short cut?

Steve Stava

You can use the transform_timestamp filter. It will let you specify "+ 2 years" for the date from the database and you will get an updated date with 2 years added to it. All you need to do then is update the row in the database with the updated value and you should be good to go
[image.png][image.png]

Tim McIntosh

Hi , 
Thanks for taking some time out of your day to reply to me, appreciate it. Understand the above solution but that would provide me with the following date in my example - "Jan 1 2002". 

I probably wasn't being explicit enough with my problem. I want to be able to replace the database date year with the current year. Here are some more examples below:

Old Date | New Date 

"Jan 1 2000" | "Jan 1 2022"
"Mar 20 1990" | "Mar 20 2022"
"Feb 16 1964" | "Feb 16 2022"

I'm hoping this makes more sense. Thanking you in advance!

Steve Stava

Does this get you closer to what you are looking to accomplish? I used the format_timestamp as a way to get the month and day of the input date (equivalent to your database date).

I then used format_timestamp to get the year from the current date (now).

Then I used the sprintf filter to re-assemble the month and day from the input date with the year from the current date.

You can see the steps and outcome in the screenshots below.

Another approach (not shown here) is to use the format_timestamp to get just the year from the input date and current date and then subtract those two value (i.e. 2001 - 2022 = 21). You then use the difference between the two years to add that different to the input date using the transform_timestamp filter.
[Screen Shot 2022-07-18 at 11.07.57 PM.png][Screen Shot 2022-07-18 at 11.08.53 PM.png][Screen Shot 2022-07-18 at 11.09.10 PM.png][Screen Shot 2022-07-18 at 11.09.23 PM.png][Screen Shot 2022-07-18 at 11.09.36 PM.png]

Tim McIntosh

Thanks . 
Yep, this is definitely the approach that I was thinking of needing to go. Thanks for pointing me in the right direction, I appreciate it.