Adding number of days to a date in SQL?-CodePudding

I have a table holding a list of accounts along with the first date on which they transacted. In all cases this date is earlier than 31/08/2020.

What I want to do is figure out how many days are between each account's first transaction and 31/08/2020, then add this number of days to 31/08/2020 to return a date.

For example, the output should look like this:

ACCOUNT_NUMBER | FIRST_TRANSACTION_DATE | MIDDLE_DATE | END_DATE
---------------|------------------------|-------------|-----------
1              | 2020-08-01             | 2020-08-31  | 2020-09-30
2              | 2020-08-11             | 2020-08-31  | 2020-09-20

For account 1, since first_transaction_date is 30 days before 31/08/20, end_date is given as 30 days after 31/08/20. For account 2, end_date is 20 days after because first_transaction_date is 20 days before, and so on.

Here is the code I have so far:

SELECT  ACCOUNT_NUMBER, FIRST_TRANSACTION_DATE, MIDDLE_DATE, MIDDLE_DATE DAYS_TO_ADD AS END_DATE
FROM
        (--SUBQUERY 1
        SELECT  ACCOUNT_NUMBER, FIRST_TRANSACTION_DATE, MIDDLE_DATE, DAYS(MIDDLE_DATE)-DAYS(FIRST_TRANSACTION_DATE) AS DAYS_TO_ADD
        FROM
                (--SUBQUERY 2
                SELECT  ACCOUNT_NUMBER, FIRST_TRANSACTION_DATE, '2020-08-31' AS MIDDLE_DATE 
                FROM    TABLE1
                )
        )
;

Subqueries 1 & 2 work, but adding middle_date days_to_add returns the error 'invalid character found in a character string argument of the function "DECFLOAT"'.

Does anyone know how I can get this to work please?

CodePudding user response：

'2020-08-31' is a varchar(10), days(middle_date) casts it to a date because days() wants a date, but middle_date days_to_add tries to cast it to a DECFLOAT, and fails.

Add date keyword before value

SELECT  ACCOUNT_NUMBER, FIRST_TRANSACTION_DATE, MIDDLE_DATE, MIDDLE_DATE DAYS_TO_ADD AS END_DATE
FROM
        (--SUBQUERY 1
        SELECT  ACCOUNT_NUMBER, FIRST_TRANSACTION_DATE, MIDDLE_DATE, DAYS(MIDDLE_DATE)-DAYS(FIRST_TRANSACTION_DATE) AS DAYS_TO_ADD
        FROM
                (--SUBQUERY 2
                SELECT  ACCOUNT_NUMBER, FIRST_TRANSACTION_DATE, date '2020-08-31' AS MIDDLE_DATE 
                FROM    TABLE1
                )
        )
;