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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×