Creating a date variable to use in SELECT statement

Question

Hi I would really appreciate some help. I would like to declare 2 variables as dates in the date format as date1 = 31-AUG-15 and date2 = 01-SEP-15 and use that within the SELECT statement so I don't need to keep typing in dates everywhere when I need to change the date. Is this possible?

SELECT TABLE1.TRANSACTION_ID, TABLE1.AMOUNT, TABLE1.MATURITY, TABLE1.TYPE,
TABLE1.EFFECTIVE_DATE, TABLE1.SETTLEMENT_DATE, TABLE2.TRANSACTION_DATE 
FROM TABLE1 
LEFT JOIN TABLE2 ON TABLE1.TRANSACTION_ID = TABLE2.TRANSACTION_ID AND TABLE2.TRANSACTION_DATE = date2
WHERE TABLE1.MATURITY > date1
AND TABLE1.EFFECTIVE_DATE > date1
AND TABLE1.SETTLEMENT_DATE = date1

You can use a .sql file to contain both the variable declaration and the sql to help reduce repetition, but I am not aware of anything that would let you define a variable, may be you can use the DUAL table on Oracle. — Pradeep Pati
– Pradeep Pati, Commented Sep 8, 2015 at 16:19
I'm just using toad to pull data from an oracle sql database table. I would really appreciate any help — robertkim
– robertkim, Commented Sep 8, 2015 at 16:20

Community · Accepted Answer · 2017-05-23 11:59:28Z

0

According to the documentation for Oracle, you should use a DECLARE statement.

This answer will provide more details on dates in particular:

DECLARE 
    date1 DATE := to_date('03/11/2011', 'dd/mm/yyyy');

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Sep 8, 2015 at 16:47

miken32

42.5k16 gold badges127 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:07:31Z

0

You did not specify what type of SQL you are using (MSSQL, MySQL, Oracle SQL, ...). I can help you with a snippet for Microsoft SQL Server.

DECLARE @date1  DATETIME,
        @date2  DATETIME

SET @date1 := '08-31-15'
SET @date2 := '09-01-15'

SELECT TABLE1.TRANSACTION_ID, TABLE1.AMOUNT, TABLE1.MATURITY,
       TABLE1.TYPE, TABLE1.EFFECTIVE_DATE, TABLE1.SETTLEMENT_DATE, TABLE2.TRANSACTION_DATE 
FROM TABLE1 
LEFT JOIN TABLE2 ON TABLE1.TRANSACTION_ID = TABLE2.TRANSACTION_ID AND TABLE2.TRANSACTION_DATE = date2
WHERE TABLE1.MATURITY > date1
      AND TABLE1.EFFECTIVE_DATE > date1
      AND TABLE1.SETTLEMENT_DATE = date1

I hope this helps. I used answers to this question: SQL How to correctly set a date variable value and use it? as a reference.

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Sep 8, 2015 at 16:36

Elvar Helgason

711 silver badge5 bronze badges

1 Comment

robertkim Over a year ago

Thanks Elvar. The table is in Oracle SQL and I am using toad to query. However, I am getting an error encountered the symbol date2 when expecting following :=,(@%; not null range default character

Collectives™ on Stack Overflow

Creating a date variable to use in SELECT statement

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related