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.