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

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 d开发者_开发百科ates? (Advantage is telling me that there is no "str()" function, and it won't let me concatenate month(mydate) with "/".

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)

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'