TO_DATE (SQL)
Synopsis
TO_DATE(dateString)
TO_DATE(dateString,format)
TODATE(...)
Description
The TO_DATE function converts date strings in various formats to a date integer value of data type DATE. This function is used to input dates in various string formats and store them using a standard InterSystems IRIS® representation.
TO_DATE returns an integer count of the number of days since December 31, 1840, where 0 represents December 31, 1840, the minimum value of -672045 represents January 1, 0001, and the maximum value of 2980013 represents December 31, 9999.
-
TO_DATE(dateString) parses the date string using the default format of DD MON YYYY.
This statement converts the date string 22 Feb 2022 to the integer 66162.
SELECT TO_DATE('22 FEB 2022')
Example: Default Date Format
-
TO_DATE(dateString,format) parses the date string using the specified format string. The date elements of dateString must correspond to the format elements of format.
This statement performs the same conversion as in the previous syntax, but it enables you to specify the date string using a custom format.
SELECT TO_DATE('2-22-22','M-DD-YY')
Examples:
-
TODATE(...) is equivalent to TO_DATE(...).
Arguments
dateString
The dateString argument is a string expression that specifies the date string to be converted to a date. The underlying data type of dateString must be CHAR or VARCHAR2.
Each character of dateString must correspond to the format string, with these exceptions:
-
Leading zeros can be included or omitted, with the exception of a dateString without separator characters.
-
Years can be specified with two digits or four digits.
-
Month names can be specified in full or as the first three letters of the name. Only the first three letters must be correct. Month names are not case-sensitive.
-
Time values appended to a date are ignored.
The earliest date you can specify is December 31, 1840, which InterSystems IRIS represents as logical integer 0. To specify earlier dates, use the Julian data format. See Julian Dates (J Format).
format
The format argument is a date string that specifies the format of the dateString. TO_DATE converts the date elements of dateString according to the format elements in the corresponding positions of format. The elements of format follow these rules:
-
Format elements are not case-sensitive.
-
Almost any sequence or number of format elements is permitted.
-
Format strings separate their elements with non-alphanumeric separator characters (for example, a space, slash, or hyphen) that match the separator characters in the dateString. This use of specified date separator characters does not depend on the DateSeparator defined for your NLS (National Language Support) locale.
-
The following date format strings do not require separator characters: MMDDYYYY, DDMMYYYY, YYYYMMDD, and YYYYDDMM. The incomplete date format YYYYMM is also supported and assumes a DD value of 01. In these formats, the MM and DD values require leading zeros.
This table lists the valid date format elements. The uppercase form is shown, but these elements are not case-sensitive.
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 can be more than three letters long and might 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 two digits of a two-digit year default to 19. |
RR / RRRR | Two-digit year to four-digit year conversion. For more details, see Two-Digit Year Conversion (RR and RRRR Formats). |
DDD | Day of the year. The count of days since January 1. See Day of the Year Conversion (DDD Format). |
J | Julian date. Used to represent dates prior to December 31, 1840. See Julian Dates (J format). |
A TO_DATE format can also include a D (day of week number), DY (day of week abbreviation), or DAY (day of week name) element. However, these format elements are not validated or used to determine the return value. For more details on these format elements, see TO_CHAR.
If you omit format, TO_DATE parses the date string using the default format of DD MON YYYY. For example, '22 Feb 2018'. To change the default date format system-wide, you can modify the TODATEDefaultFormat configuration parameter.
To view the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays the TO_DATE() Default Format setting.
Examples
Default Date Format
This statement specifies date strings that are parsed using the default date format. Both of these are converted to the DATE data type internal value of 60537:
SELECT
TO_DATE('29 September 2018'),
TO_DATE('29 SEP 2018')
This statement specifies date strings with two-digit years with format default. Note that two-digit years default to 1900 through 1999. Thus, the internal DATE value is 24012:
SELECT
TO_DATE('29 September 06'),
TO_DATE('29 SEP 06')
Specified Date Format
This statement specifies date strings in various formats. All of these are converted to the DATE data type internal value of 64701.
SELECT
TO_DATE('2018 Feb 22','YYYY MON DD'),
TO_DATE('FEBRUARY 22, 2018','month dd, YYYY'),
TO_DATE('2018***02***22','YYYY***MM***DD'),
TO_DATE('02/22/2018','MM/DD/YYYY')
This statement specifies date formats that do not require element separators. They return the date internal value of 64701.
SELECT
TO_DATE('02222018','MMDDYYYY'),
TO_DATE('22022018','DDMMYYYY'),
TO_DATE('20182202','YYYYDDMM'),
TO_DATE('20180222','YYYYMMDD')
This statement specifies the YYYYMM date format. It does not require element separators. It supplies 01 for the missing day element, returning the date 64800 (June 1, 2018):
SELECT TO_DATE('201806','YYYYMM')
Standalone Date Element Formats
In the format argument, you can specify DD, DDD, MM, or YYYY as standalone date strings. Because these format strings omit the month, year, or both the month and year, InterSystems IRIS interprets them as referring to the current month and year.
DD returns the date for the specified day in the current month of the current year. For example:
SELECT TO_DATE('24','DD')
DDD returns the date for the specified number of days elapsed in the current year. For example:
SELECT TO_DATE('300','DDD')
MM returns the date for the first day of the specified month in the current year. For example:
SELECT TO_DATE('8','MM')
YYYY returns the date for the first day of the current month of the specified year. For example:
SELECT TO_DATE('2022','YYYY')
Two-Digit Year Conversion (RR and RRRR Formats)
The YY format converts a two-digit year value to four digits by appending 19. For example, 07 becomes 1907 and 93 becomes 1993. The RR and RRRR formats provide more flexible two-digit to four-digit year conversion.
The RR format conversion is based on the current year.
-
If the current year is in the first half of a century:
-
Two-digit years from 00 through 49 are expanded to a four-digit year in the current century.
-
Two-digit years from 50 through 99 are expanded to a four-digit year in the previous century.
This statement shows the display format of dates that TO_DATE returns when the current year is between 2000 and 2050.
SELECT TO_DATE('29 September 00','DD MONTH RR'), -- 09/29/2000 TO_DATE('29 September 18','DD MONTH RR'), -- 09/29/2018 TO_DATE('29 September 49','DD MONTH RR'), -- 09/29/2049 TO_DATE('29 September 50','DD MONTH RR'), -- 09/29/1950 TO_DATE('29 September 77','DD MONTH RR') -- 09/29/1977
-
-
If the current year is in the second half of a century, all two-digit years are expanded to a four-digit year in the current century.
This statement shows the display format of dates that TO_DATE returns when the current year is between 2050 and 2099.
SELECT TO_DATE('29 September 00','DD MONTH RR'), -- 09/29/2000 TO_DATE('29 September 21','DD MONTH RR'), -- 09/29/2021 TO_DATE('29 September 49','DD MONTH RR'), -- 09/29/2049 TO_DATE('29 September 50','DD MONTH RR'), -- 09/29/2050 TO_DATE('29 September 77','DD MONTH RR') -- 09/29/2077
Using the RRRR format, you can input a mix of two-digit and four-digit years. TO_DATE passes four-digit years through unchanged. TO_DATE converts two-digit years to four-digit years by using the RR format algorithm described earlier in this example.
This statement shows the display format of dates that TO_DATE returns when the current year is between 2000 and 2050.
SELECT
TO_DATE('29 September 2021','DD MONTH RRRR'), -- 09/29/2021
TO_DATE('29 September 21','DD MONTH RRRR'), -- 09/29/2021
TO_DATE('29 September 1949','DD MONTH RRRR'), -- 09/29/1949
TO_DATE('29 September 49','DD MONTH RRRR'), -- 09/29/2049
TO_DATE('29 September 1950','DD MONTH RRRR'), -- 09/29/1950
TO_DATE('29 September 50','DD MONTH RRRR') -- 09/29/1950
Day of the Year Conversion (DDD Format)
You can use the DDD format to convert the day of the year (that is, the number of days elapsed since January 1) to an actual date. To perform this conversion:
-
The format argument must contain the DDD format element and optionally a year format such as YYYY, YY, RR, or RRRR. You can specify these elements in any order but they must include a separator character between them. If you omit the year element, then TO_DATE defaults to the current year.
-
The dateString argument must contain corresponding day and year values, where:
-
day is an integer in the range 1 through 365 (366 if year is a leap year).
-
year is a year within the standard InterSystems IRIS date range: 1841 through 9999.
-
This statement returns the 60th day of the year 2022.
SELECT TO_DATE('2022:60','YYYY:DDD') -- 03/01/2022
TO_DATE passes month elements through unchanged. If a format string contains both a DD and a DDD element, TO_DATE processes the DDD element and ignores the DD element. For example, this statement returns 2/29/2020 (the 60th day of 2020), not 12/31/2020:
SELECT TO_DATE('2020-12-31-60','YYYY-MM-DD-DDD')
TO_DATE returns a date expression containing the day of the year, not the day of the year itself. To return this day value, use TO_CHAR.
Set Default Date Column Values
When creating a table using the CREATE TABLE command, you can use the TO_DATE function to set the default value of a column. For example:
CREATE TABLE MyTable
(ID NUMBER(12,0) NOT NULL,
End_Year DATE DEFAULT TO_DATE('12-31-2021','MM-DD-YYYY') NOT NULL)
More About
Julian Dates (J Format)
The Julian date format enables you to represent dates before December 31, 1840. To use this format, specify the format argument of TO_DATE as 'J' or 'j'. Using this format, you can convert a seven-digit internal numeric value (a Julian day count) to a formatted date. For example, this statement returns 1585–01–31 in Logical or ODBC format and 01/31/1585 in Display format.
SELECT TO_DATE(2300000,'J')
The Julian day count value of 1721424 returns January 1st of Year 1 (1–01–01) in the Julian calendar. Julian day counts less than this values return BCE dates, which are displayed with the year preceded by a minus sign.
By default, the %DateOpens in a new tab data type does not represent dates prior to December 31, 1840. However, you can redefine the MINVAL parameter for this data type to permit representation of earlier dates as negative integers, with the limit of January 1, Year 1. This representation of dates as negative integers is not compatible with the Julian date format described here. For more details, see Data Types.
A Julian day count is always represented internally as a seven-digit number, with leading zeros when necessary. TO_DATE allows you to input a Julian day count without the leading zeros. The highest permitted Julian date is 5373484, which returns 12/31/9999. The lowest permitted Julian date is 0000001, which returns 01/01/-4712 (01/01/4713 BCE). Any value outside this range generates an SQLCODE -400 error.
Julian day counts prior to 1721424 (1/1/1) are compatible with other software implementations, such as Oracle. They are not identical to BCE dates in ordinary usage. In ordinary usage, there is no Year 0 and dates go from 12/31/-1 to 1/1/1. In Oracle usage, the Julian dates 1721058 through 1721423 are simply invalid, and return an error. In InterSystems IRIS, these Julian dates return the non-existent Year 0 as a place holder. Thus calculations involving BCE dates must be adjusted by one year to correspond to common usage. This should not affect the conversion of dates and Julian day counts using TO_CHAR and TO_DATE, but it might affect some calculations made using Julian day counts. Also, be aware that these date counts do not take into account changes in date caused by the Gregorian calendar reform.
TO_DATE permits you to return a date expression corresponding to a Julian day count. TO_CHAR permits you to return a Julian day count corresponding to a date expression, as shown in this example:
SELECT
TO_CHAR('1776-07-04','J') AS JulianCount, -- 2369916
TO_DATE(2369916,'J') AS JulianDate -- 1776-07-04