I have been asked to fill a field with date that is 1 month prior to a date in a another field in our database.
This would be like if the date is
1/16/13 I would then input into the new field the date of
The issue that I ran into is that some functions that are normal to native SQL databases do not exist in the same capacity in the Sybase Advantage Database I am using.
DATEADD for instance is not available from what I have experienced thus far.
So I have used the semi equivalent
TIMESTAMPADD function. I started getting an error right when I thought I had it figured out so here is where I feel the issue is, but I do not know what the issue is:
TIMESTAMPADD(SQL_TSI_MONTH, -1, memotext)
entityrole = 'MTG_PROP_FIGS'
AND fieldnum = 22
I keep getting this error:
ERROR IN SCRIPT: poQuery: Error 7200: AQE Error: State = S0000; NativeError = 2124; [iAnywhere Solutions][Advantage SQL Engine]Invalid operand for operator: <assignment>
The ideal solution would be to have a
TIMESTAMP column instead of a
MEMO, etc.) column in your database.
You have to convert from your character field to a timestamp field in order to use the
For example this will output
SELECT LEFT(CONVERT(TIMESTAMPADD(SQL_TSI_MONTH, -1, CONVERT('2013-01-16 00:00:00', SQL_TIMESTAMP)), SQL_CHAR), 10) FROM system.iota
If your date format is not in
ISO 8601 you have to reformat the date string in input and output.
If it contains
01/16/13 (leading zeros) you can just use
SUBSTRING to reformat the string:
DECLARE @Input CHAR(20); SET @Input = '01/16/13'; SET @Input = '20' + SUBSTRING(@Input, 7, 2) + '-' + SUBSTRING(@Input, 1, 2) + '-' + SUBSTRING(@Input, 4, 2) + ' 00:00:00'; SELECT LEFT(CONVERT(TIMESTAMPADD(SQL_TSI_MONTH, -1, CONVERT(@Input, SQL_TIMESTAMP)), SQL_CHAR), 10) FROM system.iota
If the database contains
1/16/13 (no leading zeros) you have to use a split string algorithm which is out of the scope of this question.
To reformat the output you can use
DECLARE @Input CHAR(20); DECLARE @Temp TIMESTAMP; SET @Input = '01/16/13'; SET @Input = '20' + SUBSTRING(@Input, 7, 2) + '-' + SUBSTRING(@Input, 1, 2) + '-' + SUBSTRING(@Input, 4, 2) + ' 00:00:00'; SET @Temp = TIMESTAMPADD(SQL_TSI_MONTH, -1, CONVERT(@Input, SQL_TIMESTAMP)) ; SELECT TRIM(CONVERT(MONTH(@Temp), SQL_CHAR)) + '/' + TRIM(CONVERT(DAY(@Temp), SQL_CHAR)) + '/' + TRIM(CONVERT(YEAR(@Temp), SQL_CHAR)) FROM system.iota
I'd suggest you first figure out how to do this with variables like in my examples. You can then combine this with your
INSERT INTO .. SELECT statement.
You should be aware that anything that isn't a valid date format may cause trouble (the whole statement not executing due to
CONVERT errors and so on).
If anything is unclear you can comment or ask another question on specific problems you're still having.