Character-to-DATE Conversion

SQL Functions, Operators, Expressions, and Predicates

Product

Teradata Database

Release Number

15.00

Language

English (United States)

Last Update

2018-09-24

dita:id

B035-1145

lifecycle

Product Category

Teradata® Database

Purpose

Converts a character string to a date value.

CAST Syntax

where:

Syntax element …	Specifies …
character_expression	a character expression to be cast to a DATE value.
data_attribute	one of the following optional data attributes: FORMAT NAMED TITLE

ANSI Compliance

This is ANSI SQL:2011 compliant.

As an extension to ANSI, CAST permits the use of data attributes, such as the FORMAT phrase that enables alternative formatting for the date data.

Teradata Conversion Syntax

where:

Syntax element …	Specifies …
character_expression	a character expression to be cast to a DATE value.
data_attribute	one of the following optional data attributes: FORMAT NAMED TITLE

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Implicit Character-to-DATE Conversion

If the string does not represent a valid date, an error is reported.

In record or indicator mode, when the DateForm mode of the session is set to ANSIDate, the string must use the ANSI DATE format.

Usage Notes

The character expression is trimmed of leading and trailing pad characters and handled as if it was a string literal in the declaration of a DATE literal.

Character-to-DATE conversion is supported for CHAR and VARCHAR types only. The source character type cannot be CLOB.

If the string can be converted to a valid DATE, then it is. Otherwise, an error is returned.

Character String Format

If the dateform of the current session is INTEGERDATE, the date representation in the character string must match the DATE output format according to the rules in the following table:

IF the statement …

THEN …

specifies a FORMAT phrase for the DATE

the character string must match that DATE format.

does not specify a FORMAT phrase

If the DATE column definition:

Specifies a FORMAT phrase, the character string must match the DATE format.

Does not specify a FORMAT phrase, the character string must match ‘YY/MM/DD’, or the current setting of the default date format in the specification for data formatting (SDF) file.

For an example, see “Example 1: IntegerDate Dateform Mode” on page 608.

If the dateform of the current session is ANSIDATE, the date representation in the character string must match the DATE output format according to the rules in the following table:

IF the statement …

THEN …

specifies a FORMAT phrase for the DATE

the character string must match that DATE format.

does not specify a FORMAT phrase

If in field mode, then if the DATE column definition:

Specifies a FORMAT phrase, the character string must match that DATA format.

Does not specify a FORMAT phrase, the character string must match the ANSI format
('YYYY-MM-DD')

If in record or indicator mode, the character string must match the ANSI format ('YYYY-MM-DD').

For an example, see “Example 2: ANSIDate Dateform Mode” on page 609.

Forcing a FORMAT on CAST for Converting Character to DATE

You can use a FORMAT phrase to convert a character string that does not match the format of the target DATE data type. A character string in a conversion that does not specify a FORMAT phrase uses the output format for the DATE data type.

For example, suppose the session dateform is INTEGERDATE and the default DATE format of the system is set to 'yyyymmdd' through the tdlocaledef utility. The following statement fails, because the character string contains separators, which does not match the default DATE format:

   SELECT CAST ('2005-01-01' AS DATE);

To override the default DATE format, and convert a character string that contains separators, specify a FORMAT phrase for the DATE target type:

   SELECT CAST ('2005-01-01' AS DATE FORMAT 'YYYY-MM-DD');

In character-to-DATE conversions, the FORMAT phrase must not consist solely of the following formatting characters:

EEEE

EEE

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

Character Strings That Omit Day, Month, or Year

If the character string and the format for a character-to-DATE conversion omits the day, month, or year, the system uses default values for the target DATE value.

IF the character string omits the …	THEN the system uses the …
day	value of 1 (the first day of the month).
month	value of 1 (the month of January).
year	current year (at the current session time zone).

Consider the following table:

   CREATE TABLE date_log

      (id INTEGER

      ,start_date DATE

      ,end_date DATE

      ,log_date DATE);

The following INSERT statement converts three character strings to DATE values. The first character string omits the day, the second character string omits the month, and the third character string omits the year. Assume the current year is 1992.

   INSERT date_log

      (1001

      ,CAST ('January 1992' AS DATE FORMAT 'MMMMBYYYY')

      ,CAST ('1992-01' AS DATE FORMAT 'YYYY-DD')

      ,CAST ('01/01' AS DATE FORMAT 'MM/DD'));

The result of the INSERT statement is as follows:

   SELECT * FROM date_log;

            id  start_date  end_date  log_date

   -----------  ----------  --------  --------

          1001    92/01/01  92/01/01  92/01/01

Example : IntegerDate Dateform Mode

For example, suppose the session dateform is INTEGERDATE, and the default DATE format of the system is set to 'yyyymmdd' through the tdlocaledef utility.

Consider the following table, where the start_date column uses the default DATE format and the end_date column uses the format 'YYYY/MM/DD':

   CREATE TABLE date_log

      (id INTEGER

      ,start_date DATE

      ,end_date DATE FORMAT 'YYYY/MM/DD');

The following INSERT statement works because the character strings match the formats of the corresponding DATE columns and Teradata Database can successfully perform implicit character-to-DATE conversion:

   INSERT INTO date_log (1099, '20030122', '2003/01/23');

To perform character-to-DATE conversion on character strings that do not match the formats of the corresponding DATE columns, you must use a FORMAT phrase:

   INSERT INTO date_log

      (1047

      ,CAST ('Jan 12, 2003' AS DATE FORMAT 'MMMBDD,BYYYY')

      ,CAST ('Jan 13, 2003' AS DATE FORMAT 'MMMBDD,BYYYY'));

Example : ANSIDate Dateform Mode

Suppose the session dateform is ANSIDATE. The default DATE format of the system is
'YYYY-MM-DD'.

Consider the following table, where the start_date column uses the default DATE format and the end_date column uses the format 'YYYY/MM/DD':

   CREATE TABLE date_log

      (id INTEGER

      ,start_date DATE

      ,end_date DATE FORMAT 'YYYY/MM/DD');

   INSERT INTO date_log (1099, '2003-01-22', '2003/01/23');

To perform character-to-DATE conversion on character strings that do not match the formats of the corresponding DATE columns, you must use a FORMAT phrase:

   INSERT INTO date_log

      (1047

      ,CAST ('Jan 12, 2003' AS DATE FORMAT 'MMMBDD,BYYYY')

      ,CAST ('Jan 13, 2003' AS DATE FORMAT 'MMMBDD,BYYYY'));

Example : Implicit Character-to-DATE Conversion

Assume that the DateForm mode of the session is set to ANSIDate.

The following CREATE TABLE statement specifies a FORMAT phrase for the DATE data type column:

   CREATE SET TABLE datetab (f1 DATE FORMAT 'MMM-DD-YYYY');

In field mode, the following INSERT statement successfully performs the character to DATE implicit conversion because the format of the string conforms to the format of the DATE column in the datetab table:

   INSERT INTO datetab ('JAN-10-1999');

In record or indicator mode, when the DateForm mode of the session is set to ANSIDate, the following INSERT statement successfully performs the character to DATE implicit conversion because the format of the string is in the ANSI DATE format:

   INSERT INTO datetab ('2002-05-10');