How do you declare a variable (specially a date) in Teradata?

General

How do you declare a variable (specially a date) in Teradata?

Hello, I'm new to Teradata and I'm curious in how to declare a variable in Teradata. In SQL server, I have the following code to declare a start and end date, but I'm not sure what the Teradata equivalent would be:

 

SQL SERVER code:

DECLARE @ANCHORDATE DATETIME

SET @ANCHORDATE  = DATEADD(DAY,-1, '01 JANUARY 2017')

DECLARE @START_DATE DATETIME

DECLARE @END_DATE DATETIME

SET @START_DATE=DATEADD(YEAR,-3,@ANCHORDATE)+1

SET @END_DATE=@ANCHORDATE

4 REPLIES

Re: How do you declare a variable (specially a date) in Teradata?

Hi,

If you are talking about a SP, then you do it this way.

DECLARE  TGTDB             VARCHAR(30)     DEFAULT  '';

--select some value to this variable from a select

  SELECT databasename   INTO :TGTDB
    FROM dbc.databases
   WHERE databasename   = :TGTDBNAME

--check for some value in it

IF ( SRCDBNAME =  '' ) THEN 
     SET MESSAGE = 'ERROR: Source DB  name cannot be empty' ;
     LEAVE StoredProcedure;
  END IF;

For date also, you can declare a char/varchar and it can be used in queries.

--Samir

Re: How do you declare a variable (specially a date) in Teradata?

What is SP? In my sql server code I eventually reference @START_DATE and @END_DATE with something similar to this:

 

SELECT *
FROM  TEST
WHERE TEST_DATE BETWEN @START_DATE AND @END_DATE

Re: How do you declare a variable (specially a date) in Teradata?

Hear another example declare/set I wrote in SQL server in the past, but I do not know what the Teradata syntax equivalent is:

 

DECLARE @START_DATE DATETIME

DECLARE @END_DATE DATETIME

SET @END_DATE = DATEADD (DAY, -1, '1 AUGUST 2015')

SET @START_DATE = DATEADD(MONTH, -12,@END_DATE)

SELECT @START_DATE AS [START DATE]

SELECT @END_DATE AS [END DATE]

 

SELECT DISTINCT *

FROM TEST

WHERE TEST_DATE between @START_DATE AND @END_DATE

Teradata Employee

Re: How do you declare a variable (specially a date) in Teradata?

you can only do this in stored procedures (SP's)

 

declare the variable at the beginning of procedure:

DECLARE lastBatchDTTM TIMESTAMP(3);

 

modify variable value just like in SQL Server:

lastBatchDTTM = current_timestamp(3);