Using CHAR_TO_TIMESTAMP to convert a String to a Timestamp
When the input string is not in the appropriate format to be CAST, you can use the
CHAR_TO_TIMESTAMP method. It has the additional advantage that you can specify which parts of the timestamp string you wish to use in subsequent processing, and create a TIMESTAMP value containing only those. To do so, you specify a template that identifies which parts you want, such as 'yyyy-MM' to use only the year and month parts.
The input-date-time string-to-be-converted can contain all or any parts of a full timestamp, that is, values for any or all of the standard elements ('yyyy-MM-dd hh:mm:ss'). If all these elements are present in your input string, and 'yyyy-MM-dd hh:mm:ss' is the template you supply, then the input-string elements are interpreted in that order as year, month, day, hour, minute, and seconds, such as in '2009-09-16 03:15:24'. The yyyy cannot be uppercase; the hh can be uppercase to mean using a 24-hour clock. For many examples of valid specifiers, see the table and examples later in this topic. For the full range of valid specifiers, see Class SimpleDateFormat on the Oracle website.
CHAR_TO_TIMESTAMP uses the template you specify as a parameter in the function call. The template causes the TIMESTAMP result to use only the parts of the input-date-time value that you specified in the template. Those fields in the resulting TIMESTAMP will then contain the corresponding data taken from your input-date-time string; fields not specified in your template will use default values (see below).
The format of the template used by CHAR_TO_TIMESTAMP is defined by Class SimpleDateFormat, at which link all the specifiers are listed, some with examples. For more information, see Date and Time Patterns (p. 146).
The function-call syntax is as follows:
CHAR_TO_TIMESTAMP('<format_string>','<input_date_time_string>')
Where <format_ string> is the template you specify for the parts of <date_time_string> you want, and
<input_date_time_string> is the original string that is being converted to a TIMESTAMP result.
Each string must be enclosed in single quotes, and each element of the <input_date_time_string> must be in the range for its corresponding element in the template. Otherwise, no result is returned.
Example 1
• The input-string-element whose position corresponds with MM must be an integer from 1 to 12, because anything else does not represent a valid month.
• The input-string-element whose position corresponds with dd must be an integer from 1 to 31, because anything else does not represent a valid day.
• However, if MM is 2, dd cannot be 30 or 31, because February never has such days.
However, for months or days, the default starting value substituted for the omitted parts is 01.
For example, using '2009-09-16 03:15:24' as your input string, you can obtain a TIMESTAMP containing only the date, with zeros for the other fields such as hours, minutes, or seconds, by specifying
CAST
CHAR_TO_TIMESTAMP('yyyy-MM-dd','2009-09-16 03:15:24').
The result would be the TIMESTAMP 2009-09-16 00:00:00.
Example 2
• If the call had kept hours and minutes in the template while omitting months, days, and seconds, as illustrated in the following call CHAR_TO_TIMESTAMP('yyyy-hh-mm','2009-09-16 03:15:24') --- then the resulting TIMESTAMP would be 2009-01-01 03:15:00.
Template Input String Output TIMESTAMP Notes
'yyyy-MM-dd
hh:mm:ss' '2009-09-16
03:15:24' '2009-09-16
03:15:24'
Input string MUST use the form 'yyyy-MM-dd hh:mm:ss' or a subset or reordering thereof; using an input string like 'Wednesday, 16 September 2009 03:15:24' will NOT work, meaning that no output will result.
'yyyy-mm' '2012-02-08
07:23:19' '2012-01-01
00:02:00'
The template above specifies only year first and minutes second, so the second element in the input string ("02") is used as minutes.
Default values are used for Month and Day ("01") and for hours and seconds ("00"). in that order, so the second element in the input string ("02") is used as seconds and the third as minutes ("08"). Default values are used for Month and Day ("01") and for hours ("00").
'MMM dd, yyyy' 'March 7, 2010' '2010-03-07
00:00:00'
MMM in the template above matches "March";
the template's 'comma space' matches the input string.
--- --- If the template lacks the comma, so
CAST
Template Input String Output TIMESTAMP Notes
must the input string, or there is no output;
--- --- If the input string lacks the comma, so must the template.
'MMM dd,' 'March 7, 2010' '1970-03-07
00:00:00'
Note that the template above doesn't use a year specifier, causing the output TIMESTAMP to use the earliest year in this epoch, 1970.
'MMM dd,y' 'March 7, 2010' '2010-03-07
00:00:00'
Using the template above, if the input string were 'March 7, 10', the output TIMESTAMP would be '0010-03-07 00:00:00'.
'M-d' '2-8' '1970-02-08
00:00:00'
Absent a yyyy specifier in the template, as above, the earliest year in this epoch (1970) is used.
An input string of '2-8-2012' would give the same result; using '2012-2-8' would give no result because 2012 is not a valid month.
'MM-dd-yyyy' '06-23-2012 both use them in the same positions. Since the template omits hours, minutes, and seconds, zeroes are used in the output TIMESTAMP.
CAST
Template Input String Output TIMESTAMP Notes
'dd-MM-yy
hh:mm:ss' '23-06-11
10:11:12' '2011-06-23
10:11:12'
You can have the specifiers in any order as long as that order matches the meaning of the input string you supply, as above.
The template and input string of the next example below have the same meaning (and the same output TIMESTAMP) as this
10:11:12' In the template used above, the order of the month and day specifiers is reversed from the example just above, and the specifier for seconds is before hours instead of after minutes; but because the input string also puts months before (compared to the prior example above) the years and months specifiers, while the input string remains the same. In this case, the output TIMESTAMP uses the first element of the input string as the years, the second as the days, and the third as the months.
CAST
Template Input String Output TIMESTAMP Notes
'dd-MM-yy hh:mm' '23-06-11
10:11:12' '2011-06-23
10:11:00'
With seconds omitted in the template, as above, the output TIMESTAMP uses 00 seconds. Any number of y specifiers produces the same result; but if the input string inadvertently uses a 1 instead of 11 for the year, as in '23-06-1 10:11:12', then the output TIMESTAMP becomes '0001-06-23 10:11:00'.
'MM/dd/yy
hh:mm:ss' '12/19/11
10:11:12'
'12/19/11 12:11:10'
'2011-12-19 10:11:12'
'2011-12-19 00:11:10'
Slashes as delimiters are fine, if template and input both use them in the same positions, as above; otherwise, no output.
Using specifier hh, input times of 12:11:10 and 00:11:10 have the same meaning as a time in the morning.
CAST
Template Input String Output TIMESTAMP Notes
'MM/dd/yy because '2011' is not a month, as required/
expected by the fail with the above template ('y/MM/
dd'), since 19 is not a valid month;
supplying '12/11/19 00:11:12' works.
'2011-12-19 12:11:12' would fail as input because dashes don't match the slashes in the template ; '2011/12/19 12:11:12' works.
Note that for times after 12 noon, that is, for afternoon and evening times, the hours specifier must be HH instead of hh, and the input string must specify the afternoon or evening hour in 24-hour clock time, 24-hours running from 00 to 23.
--- --- Using specifier HH, input times of 12:11:10 and
CAST
Template Input String Output TIMESTAMP Notes
00:11:10 have different meanings, the first as a time in the afternoon and the second as a time in the morning.
--- --- Using the specifier hh, the times from 12:00 through include '00:09:08' in the morning for both input string '12:09:08' and string '12:09:08' in the afternoon will include '12:09:08'.