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: |
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: |
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:
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) );
SELECT CAST ('10:15:12+12:30'
AS TIME(0));
SELECT CAST ('10:15:12'
AS TIME(0) WITH TIME ZONE FORMAT 'HH:MI:SSBZ');
Conversions That Include Fractional Seconds
The following rules apply to conversions that include fractional seconds:
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));
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:
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.