SQL Date | SQL string to date | To Date Function in SQL - sql - sql tutorial - learn sql




  • The TO_DATE function is used in Oracle to convert a string to a date.
  • In Oracle, TO_DATE function converts a string value to DATE data type value using the specified format.
  • In SQL Server, you can use CONVERT or TRY_CONVERT function with an appropriate date time style.

Syntax

  • The syntax of this function is as follows:
TO_DATE ( String, [Format], [NLS Setting] )
  • The most important parameter is [Format]. Valid [Format] values are as follows:
Format Description
AD
A.D.
AD indicator to use in conjunction with the year
AM
A.M.
PM
P.M.
Meridian indicator
BC
B.C.
BC indicator to use in conjunction with the year
D Day of week (1-7)
DAY Name of day
DD Day of month (1-31)
DDD Day of year (1-366)
DY Abbreviated name of day
HH Hour of day (1-12)
HH24 Hour of day (0-23)
MI Minutes (0-59)
MM Month (01-12)
MON Abbreviated name of month
MONTH Name of month
RM Month in Roman Numerals (I - XII)
RR Accepts a 2-digit input, and returns a 4-digit year.
A value between '00' and '49' returns the year in the same century.
A value between '50' and '99' returns a year in the previous century.
RRRR Accepts a 2-digit input or a 4-digit input, and returns a 4-digit year.
For 4-digit input, the same value is returned.
For 2 digit input, a value between '00' and '49' returns the year in the same century, and a value between '50' and '99' returns a year in the previous century.
SS Second (0-59)
SSSSS Seconds past midnight (0-86399)
Y Accepts a 1-digit input, and returns a 4-digit year in that decade.
YY Accepts a 2-digit input, and returns a 4-digit year in that century.
YYY Accepts a 3-digit input, and returns a 4-digit year in that millennium.
YYYY
SYYYY
Accepts a 4-digit input, and returns a 4-digits year.
  • [NLS Setting] is used to change the output format based on the NLS Territoy and NLS Language (NLS stands for National Language Support). It is optional and is rarely used.

Examples:

  • Below are some examples on using the TO_DATE function. For clarity, the results are expressed in the 'YYYY MM DD HH24:MI:SS' format (Year Month Date Hour:Minute:Second, where Hour has a value between 0 and 23):

Example 1

SELECT TO_DATE('20170625', 'YYYYMMDD') FROM DUAL;

Result:

2017 06 25 00:00:00

Example 2

SELECT TO_DATE('1999-JAN-05', 'YYYY-MON-DD') FROM DUAL;

Result:


1999 01 05 00:00:00

Example 3

SELECT TO_DATE('2005-12-12 03600', 'YYYY-MM-DD SSSSS') FROM DUAL;

Result:

2005 12 12 01:00:00
  • 3600 seconds equals to 1 hour.

Example 4

SELECT TO_DATE('2005 120 05400', 'YYYY DDD SSSSS') FROM DUAL;

Result:

2005 04 30 01:30:00
  • April 30th is the 120th day in 2005. 5400 seconds equals to 1 hour and 30 minutes.

Example 5

SELECT TO_DATE('99-OCT-27', 'YY-MON-DD') FROM DUAL;

Result:

2099 10 27 00:00:00
  • The 'YY' format converts the year to the current century

Example 6

SELECT TO_DATE('99-JAN-05', 'RR-MON-DD') FROM DUAL;

Result:

1999 01 05 00:00:00
  • The 'RR' logic converts '99' to the previous century, hence the result is 1999.

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to SQL Date | To Date Function in SQL