Caché SQL Reference
TO_TIMESTAMP
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

A date function that converts a formatted string to a timestamp.
Synopsis
TO_TIMESTAMP(date_string[,format])
Arguments
date_string A string expression to be converted to a timestamp. This expression may contain a date value, a time value, or a date and time value.
format Optional — A date and time format string corresponding to date_string. If omitted, defaults to DD MON YYYY HH:MI:SS.
Description
The TO_TIMESTAMP function converts date and time strings in various formats to a standard timestamp, with data type TIMESTAMP. TO_TIMESTAMP returns a timestamp with the following format:
yyyy-mm-dd hh:mm:ss
with leading zeroes always included. Time is specified using a 24–hour clock. By default, a returned timestamp does not include fractional seconds.
If date_string omits components of the timestamp, TO_TIMESTAMP supplies the missing components. If both date_string and format omit the year, yyyy defaults to the current year; if only date_string omits the year, it defaults to 00, which is expanded to a four-digit year according to the year format element. If a day or month value is omitted, dd defaults to 01; mm-dd defaults to 01-01. A missing time component defaults to 00. Fractional seconds are supported, but must be explicitly specified; no fractional seconds are provided by default.
TO_TIMESTAMP supports conversion of two-digit years to four digits. TO_TIMESTAMP supports conversion of 12-hour clock time to 24-hour clock time. It provides range validation of date and time element values, including leap year validation. Range validation violations generate an SQLCODE -400 error.
This function can also be invoked from Caché ObjectScript using the TOTIMESTAMP() method call:
$SYSTEM.SQL.TOTIMESTAMP(date_string,format)
The TO_TIMESTAMP function can be used in data definition when supplying a default value to a field. For example:
CREATE TABLE mytest
(ID NUMBER(12,0) NOT NULL,
End_Year DATE DEFAULT TO_TIMESTAMP('31-12-2007','DD-MM-YYYY') NOT NULL)
TO_TIMESTAMP can be used with the CREATE TABLE or ALTER TABLE ADD COLUMN statements. Only a literal value for date_string can be used in this context. For further details, refer to the CREATE TABLE command.
Related SQL Functions
Date and Time String
The date_string argument specifies a date and time string literal. If you supply a date string with no time component, TO_TIMESTAMP supplies the time value 00:00:00. If you supply a time string with no date component, TO_TIMESTAMP supplies the date of 01–01 (January 1) of the current year.
You can supply a date and time string of any kind for the input date_string. Each date_string character must correspond to the format string, with the following exceptions:
Format
A format is a string of one or more format elements specified according to the following rules:
Format Elements
The following table lists the valid date format elements for the format argument:
Element Meaning
DD Two-digit day of month (01-31). Leading zeros are not required, unless format contains no date separator characters.
MM
Two-digit month number (01-12; 01 = January). Leading zeros are not required, unless format contains no date separator characters.
In Japanese and Chinese, a month number consists of a numeric value followed by the ideogram for “month”.
MON Abbreviated name of month, as specified by the MonthAbbr property in the current locale. By default, in English this is the first three letters of the month name. In other locales, month abbreviations may be more than three letters long and/or may not consist of the first letters of the month name. A period character is not permitted. Not case-sensitive.
MONTH Full name of the month, as specified by the MonthName property in the current locale. Not case-sensitive.
YYYY Four-digit year.
YY Last two digits of the year. The first 2 digits of a YY 2-digit year default to 19.
RR / RRRR Two-digit year to four-digit year conversion. (See below.)
DDD Day of the year. The number of days since January 1. (See below.)
HH Hour, specified as either 01–12 or 00–23, depending on whether a meridian indicator (AM or PM) is specified. Can be specified as HH12 or HH24.
MI Minute, specified as 00–59.
SS Second, specified as 00–59.
FF Fractions of a second. FF indicates that one or more fractional digits are provided; date_string can specify any number of fractional digits. TO_TIMESTAMP returns exactly the fractional value explicitly supplied in date_string; trailing zeroes are neither padded nor truncated. It provides a decimal separator format character, when necessary. By default, a Caché timestamp does not include fractional seconds.
AM / PM Meridian indicator, specifies a 12–hour clock. (See below.)
A.M. / P.M. Meridian indicator (with periods), specifies a 12–hour clock. (See below.)
A TO_TIMESTAMP format can also include a D (day of week number), DY (day of week abbreviation), or DAY (day of week name) element to match the input date_string. However, these format elements are not validated or used to determine the return value. For further details on these format elements, refer to TO_CHAR.
Two-Digit Year Conversion (RR and RRRR formats)
The RR format provides two-digit to four-digit year conversion. This conversion is based on the current year. If the current year is in the first half of a century (for example, 2000 through 2050), two-digit years from 00 through 49 are expanded to a four-digit year in the current century, and two-digit years from 50 through 99 are expanded to a four-digit year in the previous century. If the current year is in the second half of a century (for example, 2050 through 2099), all two-digit years are expanded to a four-digit year in the current century. This expansion of two-digit years to four-digit years is shown in the following example:
SELECT TO_TIMESTAMP('29 September 00','DD MONTH RR'),
       TO_TIMESTAMP('29 September 08','DD MONTH RR'),
       TO_TIMESTAMP('29 September 49','DD MONTH RR'),
       TO_TIMESTAMP('29 September 50','DD MONTH RR'),
       TO_TIMESTAMP('29 September 77','DD MONTH RR')
 
The RRRR format permits you to input a mix of two–digit and four-digit years. Four-digit years are passed through unchanged (the same as YYYY). Two-digit years are converted to four-digit years, using the RR format algorithm. This is shown in the following example:
SELECT TO_TIMESTAMP('29 September 2008','DD MONTH RRRR')AS FourDigit,
       TO_TIMESTAMP('29 September 08','DD MONTH RRRR') AS TwoDigit,
       TO_TIMESTAMP('29 September 1949','DD MONTH RRRR') AS FourDigit,
       TO_TIMESTAMP('29 September 49','DD MONTH RRRR') AS TwoDigit,
       TO_TIMESTAMP('29 September 1950','DD MONTH RRRR') AS FourDigit,
       TO_TIMESTAMP('29 September 50','DD MONTH RRRR') AS TwoDigit
 
Day of the Year (DDD format)
You can use DDD to convert the day of the year (number of days elapsed since January 1) to an actual date. The format string DDD YYYY must be paired with a corresponding date_string consisting of an integer number of days and a four-digit year. (Two-digit years must be specified as RR (not YY) when used with DDD.) The format string DDD defaults to the current year. The number of elapsed days must be a positive integer in the range 1 through 365 (366 if YYYY is a leap year). The four-digit year must be within the standard Caché date range: 1841 through 9999. (If you omit the year, it defaults to the current year.) The DDD and year (YYYY, RRRR, or RR) format elements can be specified in any order; a separator character between them is mandatory; this separator can be a blank space. The following example shows this use of Day of the Year:
SELECT TO_TIMESTAMP('2008:160','YYYY:DDD')
 
If a format string contains both a DD and a DDD element, the DDD element is dominant. This is shown in the following example, which returns 2008-02-29 00:00:00 (not 2008-12-31 00:00:00):
SELECT TO_TIMESTAMP('2008-12-31-60','YYYY-MM-DD-DDD')
 
TO_TIMESTAMP permits you to return a date expression corresponding to a day of the year. TO_CHAR permits you to return the day of the year corresponding to a date expression.
Dates Before 1841
TO_TIMESTAMP cannot represent a date before December 31, 1840. Attempted to input such a date results in an SQLCODE -400 error. The TO_DATE function provides a Julian date format for this purpose. Julian date conversion converts a seven-digit internal positive integer value (a Julian day count) to a display-format or ODBC-format date. Time values are not supported for Julian dates.
12-Hour Clock Time
A TIMESTAMP always represents time using a 24-hour clock. A date_string may represent time using a 12-hour clock or a 24-hour clock. TO_TIMESTAMP assumes a 24-hour clock, unless one of the following applies:
Examples
The following embedded SQL example specifies date strings in various formats. The first one uses the default format, the others specify a format. All of these convert date_string to the timestamp value of 2007–06–29 00:00:00:
  &sql(SELECT
       TO_TIMESTAMP('29 JUN 2007'),
       TO_TIMESTAMP('2007 Jun 29','YYYY MON DD'),
       TO_TIMESTAMP('JUNE 29, 2007','month dd, YYYY'),
       TO_TIMESTAMP('2007***06***29','YYYY***MM***DD'),
       TO_TIMESTAMP('06/29/2007','MM/DD/YYYY'),
       TO_TIMESTAMP('29/6/2007','DD/MM/YYYY')
    INTO :a,:b,:c,:d,:e,:f)
  IF SQLCODE=0 { WRITE !,a,!,b,!,c,!,d,!,e,!,f }
  ELSE { WRITE "SQLCODE error:",SQLCODE }
 
The following example specifies the YYYYMM date format. It does not require element separators. TO_TIMESTAMP supplies the missing day and time values:
 SELECT TO_TIMESTAMP('200706','YYYYMM')
 
This example returns the timestamp 2007–06–01 00:00:00.
The following example specifies just the HH:MI:SS.FF time format. TO_TIMESTAMP supplies the missing date value. In each case, this example returns the date of 2009–01–01 (where 2009 is the current year):
SELECT TO_TIMESTAMP('11:34','HH:MI:SS.FF'),
       TO_TIMESTAMP('11:34:22','HH:MI:SS.FF'),
       TO_TIMESTAMP('11:34:22.00','HH:MI:SS.FF'),
       TO_TIMESTAMP('11:34:22.7','HH:MI:SS.FF'),
       TO_TIMESTAMP('11:34:22.7000000','HH:MI:SS.FF')
 
Note that fractional seconds are passed through exactly as specified, with no padding or truncation.
See Also