Most of us play with dates pretty regularly in SQL adding days here, finding how many days between dates. What about if you need to start adding years. Adding 365 days will work but has it’s own issues and isn’t very accurate.
I wrote the following example of how to add years to a date field. This example was written for teradata but should be fine for mysql andoracle.
CAST
(TRIM(CAST
||
((TRIM(EXTRACT(YEAR FROM your_field)) + 100) AS INTEGER)‘-‘||CASE
WHEN
TRIM(EXTRACT(MONTH FROM your_field)) < 10 THEN TRIM(‘0’||TRIM(EXTRACT(MONTH FROMyour_field)))ELSE
TRIM(EXTRACT(MONTH FROM your_field))END
||
‘-‘||CASE
WHEN
TRIM(EXTRACT(DAY FROM your_field)) < 10 THEN TRIM(‘0’||TRIM(EXTRACT(DAY FROMyour_field)))ELSE
TRIM(EXTRACT(DAY FROM your_field))END
)AS DATE FORMAT ‘yyyy-mm-dd’) AS New_Year_Added;
Essentailly all the script does is break the DATE field up is days, months and years, adds to the years then puts it all back together. I wrote this script specifically to correct a 2 digit year error on a data import e.g. 01/01/09 was imported as 1909-01-01.