TO_DATE (SQL)

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).

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.

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.

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')

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')

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')

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

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.

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)

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

• TO_DATE converts a formatted date string to a date integer.

• TO_CHAR performs the reverse operation. It converts a date integer to a formatted date string.

• TO_TIMESTAMP converts a formatted date and time string to a standard timestamp.

• CAST and CONVERT perform DATE data type conversion.