DATE
Purpose
Returns the current date.
Syntax
where:
|
Syntax element … |
Specifies … |
|
AT LOCAL |
that the value returned is constructed from the session time and session time zone if the DBS Control flag TimeDateWZControl is enabled. If TimeDateWZControl is disabled, the value returned is constructed from the time value local to the Teradata Database server and the session time zone. |
|
AT [TIME ZONE] expression |
that the time zone displacement defined by expression is used. The data type of expression should be INTERVAL HOUR(2) TO MINUTE or it must be a data type that can be implicitly converted to INTERVAL HOUR(2) TO MINUTE. For details, see “AT LOCAL and AT TIME ZONE Time Zone Specifiers” on page 827. |
|
AT [TIME ZONE] time_zone_string |
that time_zone_string is used to determine the time zone displacement. For details, see “AT LOCAL and AT TIME ZONE Time Zone Specifiers” on page 827. |
ANSI Compliance
This is a Teradata extension to the ANSI SQL:2011 standard.
For the ANSI-compliant syntax and behavior for the equivalent function, see “CURRENT_DATE” on page 349.
The AT clause is ANSI SQL:2011 compliant.
As an extension to ANSI, you can specify an AT clause after the DATE function, and you can specify the time zone displacement using additional expressions besides an INTERVAL expression.
Usage Notes
DATE returns the current date at the time when the request started. If DATE is invoked more than once during the request, the same date is returned. The date returned does not change during the duration of the request.
If you specify DATE without the AT clause or DATE AT LOCAL, then the value returned depends on the setting of the DBS Control flag TimeDateWZControl as follows:
For more information, see “DBS Control (dbscontrol)” in Utilities: Volume 1 (A-K).
DATE returns a value that is adjusted to account for the start and end of daylight saving time (DST) only in the following cases:
For more information about time zone strings, see “AT LOCAL and AT TIME ZONE Time Zone Specifiers” on page 827.
DATE cannot appear as the first argument in a user-defined method invocation.
Result Type and Attributes
The default format of DATE depends on the value of the Dateform mode. For more information on default data type formats, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals. The data type, format, and title for DATE are as follows:
|
Data Type |
Format |
Title |
|
|
Dateform Mode |
DATE Function Format |
||
|
DATE |
INTEGERDATE |
the default format for DATE data types as specified in the SDF. |
Date |
|
ANSIDATE |
'YYYY-MM-DD' |
||
DATE versus CURRENT_DATE
DATE is deprecated. Use the ANSI SQL:2011 compliant CURRENT_DATE function instead. See “CURRENT_DATE” on page 349.
Example
This example assumes that the default format for DATE values is 'yy/mm/dd'. Consider the following statements:
SET TIME ZONE INTERVAL '01:00' HOUR TO MINUTE;
SELECT DATE AT TIME ZONE INTERVAL -'08:00' HOUR TO MINUTE;
SELECT DATE AT INTERVAL -'08:00' HOUR TO MINUTE;
SELECT DATE AT TIME ZONE INTERVAL -'08' HOUR;
SELECT DATE AT INTERVAL -'08' HOUR;
SELECT DATE AT TIME ZONE '-08:00';
SELECT DATE AT '-08:00';
SELECT DATE AT TIME ZONE '-8';
SELECT DATE AT '-8';
SELECT DATE AT TIME ZONE -8;
SELECT DATE AT -8;
SELECT DATE AT -8.0;
The above SELECT statements return the current date based on the time zone displacement, INTERVAL -'08:00' HOUR TO MINUTE. If the current timestamp at UTC is TIMESTAMP '2008-06-01 06:30:00.000000+00:00', these SELECT statements would return '08/05/31' as the date.
If the SELECT statement was specified without an AT clause or with an AT LOCAL clause,
and the DBS Control flag TimeDateWZControl is enabled, the statement would return
'08/06/01' as the current date based on the current session time and time zone displacement,
INTERVAL '01:00' HOUR TO MINUTE. For example:
SELECT DATE;
SELECT DATE AT LOCAL;
The date returned is not adjusted to account for the start or end of daylight saving time.
Example
The following queries return the current date at the time zone displacement based on the time zone string, 'America Pacific'. Teradata Database determines the time zone displacement based on the time zone string and the CURRENT_TIMESTAMP AT '00:00' (that is, at UTC). The date returned is automatically adjusted to account for the start and end of daylight saving time.
SELECT DATE AT TIME ZONE 'America Pacific';
SELECT DATE AT 'America Pacific';
Example
Use the FORMAT phrase to change the presentation:
SELECT DATE (FORMAT 'mm-dd-yy');
Date
--------
03-30-96
Example
Another form gives:
SELECT DATE (FORMAT 'mmmbdd,byyyy');
Date
------------
Mar 30, 1996