TIMESTAMP-to-DATE Conversion Example 5 - Teradata Vantage

Consider the following statements:

SET TIME ZONE +1;
SELECT CAST((TIMESTAMP '2008-06-01 08:30:00' AT TIME ZONE -8)
   AS DATE AT SOURCE TIME ZONE);

This SELECT statement returns the date for time zone displacement, INTERVAL -'08:00' HOUR TO MINUTE ('08/05/31)'. Without an AT clause or with an AT LOCAL clause, the statement returns '08/06/01' for the current session time zone displacement, INTERVAL HOUR '01:00' MINUTE.

The following shows the result of the SELECT statement if the AT clause was not specified:

SET TIME ZONE INTERVAL '01:00' HOUR TO MINUTE;
SELECT TIMESTAMP '2008-06-01 08:30:00'
   AT TIME ZONE INTERVAL -'08:00' HOUR TO MINUTE;

2008-06-01 08:30:00 AT TIME ZONE INTERVAL -8:00 HOUR TO MINUTE
--------------------------------------------------------------
                                     2008-05-31 23:30:00-08:00
SELECT CAST((TIMESTAMP '2008-06-01 08:30:00'
   AT TIME ZONE INTERVAL -'08:00' HOUR TO MINUTE) AS DATE);
2008-06-01 08:30:00 AT TIME ZONE INTERVAL -8:00 HOUR TO MINUTE
--------------------------------------------------------------                                                      08/06/01

The following shows the result of the SELECT statement if the AT clause was not specified, and the current session time zone displacement is INTERVAL -'08:00' HOUR TO MINUTE.

SET TIME ZONE INTERVAL -'08:00' HOUR TO MINUTE;
SELECT CAST((CAST(TIMESTAMP '2008-06-01 08:30:00+01:00' 
   AS TIMESTAMP(0)) AT TIME ZONE INTERVAL -'08:00' HOUR TO MINUTE) 
   AS DATE);

2008-06-01 08:30:00+01:00 AT TIME ZONE INTERVAL -8:00 HOUR TO MINUTE
--------------------------------------------------------------------
                                                            08/05/31