15.00 - CURRENT_DATE - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

CURRENT_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

CURRENT_DATE and the AT clause are ANSI SQL:2011 compliant.

As an extension to ANSI, you can specify an AT clause after the CURRENT_DATE function, and you can specify the time zone displacement using additional expressions besides an INTERVAL expression.

Usage Notes

CURRENT_DATE returns the current date at the time when the request started. If CURRENT_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 CURRENT_DATE without the AT clause or CURRENT_DATE AT LOCAL, then the value returned depends on the setting of the DBS Control flag TimeDateWZControl as follows:

  • If the TimeDateWZControl flag is enabled, CURRENT_DATE returns a date constructed from the session time and session time zone.
  • If the TimeDateWZControl flag is disabled, CURRENT_DATE returns a date constructed from the time value local to the Teradata Database server and the session time zone.
  • For more information, see “DBS Control (dbscontrol)” in Utilities: Volume 1 (A-K).

    CURRENT_DATE returns a value that is adjusted to account for the start and end of daylight saving time (DST) only in the following cases:

  • CURRENT_DATE is specified with AT [TIME ZONE] time_zone_string, where time_zone_string follows different DST and standard time zone displacements.
  • CURRENT_DATE is specified with AT LOCAL or without an AT clause and the session time zone was defined with a time zone string that follows different DST and standard time zone displacements.
  • For more information about time zone strings, see “AT LOCAL and AT TIME ZONE Time Zone Specifiers” on page 827.

    Result Type and Attributes

    The result data type, format, and title for CURRENT_DATE are:

     

    Data Type

    Format

    Title

    DATE

    Default format for the DATE data type when the Dateform mode is set to IntegerDate.

    For more information on the default formats, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

    Date

    To convert CURRENT_DATE, use Teradata explicit conversion syntax or ANSI CAST syntax. For an example that uses Teradata explicit conversion syntax to change the default output format, see “Example 3: Changing the Default Output Format” on page 358.

    CURRENT_DATE versus DATE

    CURRENT_DATE provides similar functionality to the Teradata function DATE using ANSI-compliant syntax. For information on the Teradata DATE function, see “DATE” on page 365.

    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 CURRENT_DATE AT TIME ZONE INTERVAL -'08:00' HOUR TO MINUTE;
       SELECT CURRENT_DATE AT INTERVAL -'08:00' HOUR TO MINUTE;
       SELECT CURRENT_DATE AT TIME ZONE INTERVAL -'08' HOUR;
       SELECT CURRENT_DATE AT INTERVAL -'08' HOUR;
       SELECT CURRENT_DATE AT TIME ZONE '-08:00';
       SELECT CURRENT_DATE AT '-08:00';
       SELECT CURRENT_DATE AT TIME ZONE '-8';
       SELECT CURRENT_DATE AT '-8';
       SELECT CURRENT_DATE AT TIME ZONE -8;
       SELECT CURRENT_DATE AT -8;
       SELECT CURRENT_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 CURRENT_DATE;
       SELECT CURRENT_DATE AT LOCAL;

    The date returned is not adjusted to account for the start or end of daylight saving time.

    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 CURRENT_DATE AT INTERVAL '09:00' HOUR TO MINUTE;

    The above SELECT statement returns the current date based on the time zone displacement, INTERVAL '09:00' HOUR TO MINUTE. If the current timestamp at UTC is TIMESTAMP '2008-06-01 19:30:00.000000+00:00', the SELECT statement would return '08/06/02' 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.

    The date returned is not adjusted to account for the start or end of daylight saving time.

    Example  

    This example assumes that the default format for DATE values is 'yy/mm/dd'. Consider the following statements:

       SET TIME ZONE INTERVAL '10:00' HOUR TO MINUTE;
     
       SELECT CURRENT_DATE AT '05:45';
       SELECT CURRENT_DATE AT 5.75;

    The above SELECT statements return the current date based on the time zone displacement, INTERVAL '05:45' HOUR TO MINUTE. If the current timestamp at UTC is TIMESTAMP '2008-06-01 17:30:00.000000+00:00', the SELECT statements would return '08/06/01' 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/02' as the current date based on the current session time and time zone displacement, INTERVAL '10:00' HOUR TO MINUTE.

    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 CURRENT_DATE AT TIME ZONE 'America Pacific';
       SELECT CURRENT_DATE AT 'America Pacific';

    Example : Changing the Default Output Format

    To change the default output format of the CURRENT_DATE result, use Teradata explicit conversion syntax and specify the FORMAT phrase. For example, the following statement requests the current date and specifies a format that is different from the default:

       SELECT CURRENT_DATE (FORMAT 'MMMBDD,BYYYY');

    The result is similar to:

               Date
       ------------
       May 31, 2007

    For more information on Teradata explicit conversion syntax, see “Teradata Conversion Syntax in Explicit Data Type Conversions” on page 593. For more information on default data type formats and the FORMAT phrase, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.