Declare a Date variable at start of SQL Script

General
Enthusiast

Declare a Date variable at start of SQL Script

Hi,

To avoid entering a date parameter many times, in many different formats to my script, I want to declare a date variable at the start.

I would then use the CAST function to change the formats to suit the task.

I'm using SQL Assistant 13:11 with Windows XP if that helps.

The code below is an example only of how I see this working, just to give you an idea of what I'm after.

Thanks

DECLARE      Date_Tm   AS  '2012-12-31 23:59:59'
--------------------------------------------------------------------------
CREATE TABLE       SOME_DATA             AS
 CAST((Date_Tm (FORMAT 'YYYY-MM-DD')) AS VARCHAR(10)) AS Yr_Mon_Dy
,CAST((Date_Tm (FORMAT 'YYYYMM')) AS CHAR(6)) AS Yr_Mon
...
...
... etc etc ...
1 REPLY
Senior Apprentice

Re: Declare a Date variable at start of SQL Script

In SQL Assistant simply use a parameter, i.e. "?Date_tm" instead of "Date_tm":

 SELECT ?CAST((Date_Tm (FORMAT 'YYYY-MM-DD')) AS VARCHAR(10)) AS Yr_Mon_Dy
,?CAST((Date_Tm (FORMAT 'YYYYMM')) AS CHAR(6)) AS Yr_Mo


SQLA will prompt for the value, simply enter it including DATE and single quotes: DATE '2013-06-02' 

Dieter