Character-to-TIMESTAMP Conversion - Teradata Database

Character-to-TIMESTAMP Conversion

Purpose

Converts a character data string to a TIMESTAMP or TIMESTAMP WITH TIME ZONE value.

CAST Syntax

where:

Syntax element …	Specifies …
character_expression	a character expression to be cast to a TIMESTAMP type.
fractional_seconds_precision	a single digit representing the number of significant digits in the fractional portion of the SECOND field. Values for fractional_seconds_precision range from 0 through 6 inclusive. The default precision is 6.
timestamp_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 time data.

Note: TIMESTAMP (without time zone) is not ANSI SQL:2011 compliant. Teradata Database internally converts a TIMESTAMP value to UTC based on the current session time zone or on a specified time zone.

Teradata Conversion Syntax

where:

Syntax element …	Specifies …
character_expression	a character expression to be cast to a TIMESTAMP type.
data_attribute	one of the following optional data attributes: FORMAT NAMED TITLE
fractional_seconds_precision	a single digit representing the number of significant digits in the fractional portion of the SECOND field. Values for fractional_seconds_precision range from 0 through 6 inclusive. The default precision is 6.

ANSI Compliance

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

Implicit Character-to-TIMESTAMP Conversion

In field mode, the string must conform to the format of the target TIMESTAMP type.

In record or indicator mode, the string must use the ANSI TIMESTAMP format.

Usage Notes

The source expression is trimmed of leading and trailing pad characters and then handled as if it were a string literal in the declaration of a TIMESTAMP string literal.

Character-to-TIMESTAMP conversion is supported for CHAR and VARCHAR types only. You cannot convert a character data type of CLOB or GRAPHIC to TIMESTAMP.

If the contents of the string can be converted to a valid TIMESTAMP value, then the conversion is performed; otherwise an error is returned.

You can use a FORMAT phrase to specify an explicit format for the TIMESTAMP target data type. A conversion that does not specify a FORMAT phrase uses the default format for the TIMESTAMP data type.

IF the character string is converted to …	THEN the default format …
TIMESTAMP	does not use the time zone formatting character and does not display a time zone.
TIMESTAMP WITH TIME ZONE	uses the time zone formatting character to display the time zone.

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

Example  

The following query returns ‘2007-12-31 23:59:59.999999-08:00’.

   SELECT CAST('2007-12-31 23:59:59.999999' 

   AS TIMESTAMP(6) WITH TIME ZONE);

Notice that the source character string did not need to have explicit Time Zone fields for this conversion to work properly.

Conversions That Include Time Zone

The following rules apply to character-to-TIMESTAMP conversions that include time zone information:

If the target data type does not specify a time zone, for example, TIMESTAMP(0), the source character string may contain a time zone of the format +hh:mi or -hh:mi, but only if it appears immediately before or immediately after the time.

For example, the following conversion is successful:

SELECT CAST ( '2008-09-19 11:23:44-02:00'

AS TIMESTAMP(0) FORMAT 'Y4-MM-DDBHH:MI:SSBZ' );

The following conversion is not successful because of the blank separator character between the time zone and the time:

SELECT CAST ( '2008-01-19 +02:00 11:23:44'

AS TIMESTAMP(0) FORMAT 'Y4-MM-DDBZBHH:MI:SS' );

If the source character string contains a time zone, and the target data type does not specify a time zone, the conversion uses the time zone in the character string to convert the character string to Universal Coordinated Time (UTC). This is done whether or not the FORMAT phrase contains the time zone formatting character.

SELECT CAST ('2002-02-20 10:15:12+12:30' AS TIMESTAMP(0));

If the target FORMAT phrase includes time zone formatting characters, and the source character string does not contain a time zone, the output includes the session time zone. This is done whether or not the target data type specifies a time zone.

SELECT CAST ('2002-02-20 10:15:12' 

AS TIMESTAMP(0) WITH TIME ZONE FORMAT 'Y4-MM-DDBHH:MI:SSBZ');

If both the source character string and the target data type do not specify a time zone, the source character string is internally converted to UTC based on the current session time zone.

Conversions That Include Fractional Seconds

The following rules apply to conversions that include fractional seconds:

The fractional seconds precision in the source character string must be less than or equal to the fractional seconds precision specified by the target type.

SELECT CAST('2002-01-01 12:30:25.44' AS TIMESTAMP(3));

If no fractional seconds appear in the source character string, then the fractional seconds precision is always less than or equal to the target data type fractional seconds precision, because the valid range for the precision is zero to six, where the default is six.

SELECT CAST('2002-01-01 12:30:25' AS TIMESTAMP(3));

If the target data type is defined by a FORMAT phrase, the fractional seconds precision formatting characters must be greater than or equal to the precision specified by the data type.

SELECT CAST('12-02-07 12:30:25' AS TIMESTAMP(3) 

FORMAT 'DD-MM-YYBHH:MI:SSDS(3)');

A FORMAT phrase must specify a fractional seconds precision of six if the target data type does not specify a fractional seconds precision, because the default precision is six.

SELECT CAST('12-02-07 12h:15.12s:30m' 

AS TIMESTAMP FORMAT 'DD-MM-YYBHHh:SSDS(6)s:MIm');

Character Strings That Omit Day, Month, Year, Hour, Minute, or Second

If the character string in a character-to-TIMESTAMP conversion omits the day, month, year, hour, minute, or second, the system uses default values for the target TIMESTAMP 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.
hour	value of 0.
minute
second

Consider the following table:

   CREATE TABLE timestamp_log

   (id INTEGER, start_ts TIMESTAMP, end_ts TIMESTAMP);

The following INSERT statement converts two character strings to TIMESTAMP values. Both strings omit the hour, minute, and second. Additionally, the first character string omits the day and the second character string omits the month.

   INSERT timestamp_log

      (1001

      ,CAST ('January 2006' AS TIMESTAMP FORMAT 'MMMMBYYYY')

      ,CAST ('2006-01' AS TIMESTAMP FORMAT 'YYYY-DD'));

The result of the INSERT statement is as follows:

  SELECT * FROM timestamp_log;

           id                    start_ts                      end_ts

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

         1001  2006-01-01 00:00:00.000000  2006-01-01 00:00:00.000000

Here is an INSERT statement where both character strings omit the year. Additionally, the first character string omits the hour and the second character string omits the minute. Assume the current year is 2003.

   INSERT timestamp_log

      (1002

      ,CAST ('January 23 04:05' AS TIMESTAMP FORMAT 'MMMMBDDBMI:SS')

      ,CAST ('01-23 04:05' AS TIMESTAMP FORMAT 'MM-DDBHH:SS'));

The result of the INSERT statement is as follows:

  SELECT * FROM timestamp_log WHERE id = 1002;

           id                    start_ts                      end_ts

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

         1001  2003-01-23 00:04:05.000000  2003-01-23 04:00:05.000000

 

Restrictions on FORMAT Phrase

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

EEEE

E4

EEE

E3

T

Z

Related Topics

For details on data types and data attributes, see SQL Data Types and Literals.