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 12/16/12
.
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:
INSERT INTO
Normalization
(
memotext
)
SELECT
TIMESTAMPADD(SQL_TSI_MONTH, -1, memotext)
FROM
eqanswer
WHERE
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 CHAR
(or MEMO
, etc.) column in your database.
You have to convert from your character field to a timestamp field in order to use the TIMESTAMPADD
function.
For example this will output 2012-12-16
:
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 MONTH
, DAY
and YEAR
:
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.