how can I add 100 years to a date in Advantage Database

Question

I have a bunch of records in my Advantage Database that ended up with the year 1909, rather than 2009. How can run an update statement that will add 100 years to each of these dates? (Advantage is telling me that there is no "str()" function, and it won't let me concatenate month(mydate) with "/".

Answer 1

My SQL is rusty but Advantage Database seems to support DATEADD. So... uh... something like this?

UPDATE mytable
SET field = DATEADD(Year, 100, field)
FROM mytable
WHERE field < '19100101'

Answer 2

You could use the following

UPDATE mytable
SET mydate =  CAST( TIMESTAMPADD( SQL_TSI_YEAR, 100, datefield ) as SQL_DATE )
WHERE YEAR( datefield ) = 1909

(if you have a timestamp field and not a date field you can leave out the CAST ... AS SQL_DATE)

---

To concatenate you must concatenate strings to change to a string you can use CAST or CONVERT

UPDATE mytable
SET datefield = CAST ( TRIM( CAST( MONTH(datefield) AS SQL_CHAR ) ) + '/' + TRIM( CAST( DAYOFMONTH( datefield ) AS SQL_CHAR ) ) + '/2009' AS SQL_DATE )
WHERE YEAR( datefield ) = 1909

(if you have a timestamp field and not a date field you can leave out the CAST ... AS SQL_DATE, but then you need to re-add in the time)