15.00 - Character-to-TIME Conversion - Teradata Database

Character-to-TIME Conversion

Purpose

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

CAST Syntax

where:

Syntax element …	Specifies …
character_expression	a character expression to be cast to a TIME 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.
time_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 output formatting for the time data.

Note: TIME (without time zone) is not ANSI SQL:2011 compliant. Teradata Database internally converts a TIME 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 TIME 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-TIME Conversion

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

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

Usage Notes

The character value is trimmed of leading and trailing pad characters and handled as if it were a string literal in the declaration of a TIME string literal.

If the contents of the string can be converted to a valid TIME, the conversion is made; otherwise, an error is returned to the application.

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

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

IF the character string is converted to …	THEN the default format …
TIME	does not use the time zone formatting character and does not display a time zone.
TIME 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.

Conversions That Include Time Zone

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

If the target data type does not specify a time zone, for example, TIME(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 ( '-02:0011:23:44'

AS TIME(0) );

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

SELECT CAST ( '+02:00 11:23:44.56'

AS TIME(2) );

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

SELECT CAST ('10:15:12+12:30' 

AS TIME(0));

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

SELECT CAST ('10:15:12' 

AS TIME(0) WITH TIME ZONE FORMAT 'HH: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('12:30:25.44' AS TIME(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('12:30:25' AS TIME(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('12h:15.12s:30m' 

AS TIME(4) FORMAT 'HHh:SSDS(4)s:MIm');

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:30:25' AS TIME FORMAT 'HH:MI:SSDS(6)');

Character Strings That Omit Hour, Minute, or Second

If the character string in a character-to-TIME conversion omits the hour, minute, or second, the system uses default values for the target TIME value.

IF the character string omits the …	THEN the system uses the …
hour	value of 0.
minute
second

Consider the following table:

   CREATE TABLE time_log

      (id INTEGER 

      ,start_time TIME 

      ,end_time TIME

      ,log_time TIME);

The following INSERT statement converts three character strings to TIME values. The first character string omits the hour, the second character string omits the minute, and the third character string omits the second.

   INSERT time_log

      (1001

      ,CAST ('01:02.030405' AS TIME FORMAT 'MI:SS.S(6)')

      ,CAST ('01:02.030405' AS TIME FORMAT 'HH:SS.S(6)')

      ,CAST ('01:02' AS TIME FORMAT 'HH:MI'));

The result of the INSERT statement is as follows:

   SELECT * FROM time_log;

            id       start_time         end_time         log_time

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

          1001  00:01:02.030405  01:00:02.030405  01:02:00.000000

FORMAT Phrase Restrictions

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

Z

T

Example : Fractional Seconds

This query returns the value ‘12:23:39.999900’ (with the fractional seconds extended to 6 places as requested by CASTing to a TIME(6) type).

   SELECT CAST(' 12:23:39.9999 '

   AS TIME(6));

Example : Truncation of Non-pad Character Data

This query returns an error because the requested conversion requires truncation of non-pad character data.

   SELECT CAST(' 12:23:39.9999 '

   AS TIME(3));

Example : Non Valid MINUTE Value

This query returns an error because the MINUTE value of 63 is not valid.

   SELECT CAST(' 12:63:39.9999 '

   AS TIME(6));

Example : FORMAT Phrase

This query returns the value '15h33m'.

   SELECT CAST('15h33m'

   AS TIME(0) FORMAT 'HHhMIm');

Example : Implicit Conversion of Character to TIME

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

   CREATE SET TABLE timetab (f1 TIME(0) FORMAT 'TBHHhMImSSs');

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

   INSERT INTO timetab ('AM 10h20m30s');

In record or indicator mode, the following INSERT statement successfully performs the character to TIME implicit conversion because the format of the string is in the ANSI TIME format:

   INSERT timetab ('11:23:34');

Related Topics

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