CodeNewbie Community 🌱

Cover image for Oracle TO_DATE Function - Complete Guide
Hassan Abd Elrahman
Hassan Abd Elrahman

Posted on

Oracle TO_DATE Function - Complete Guide

In this tutorial, we are going to explain how to use Oracle TO_DATE function with basic syntax and many examples for better understanding.

How to convert varchar to date in oracle? Oracle TO_DATE function is used to convert a character string that is one of the data types (CHAR, VARCHAR2, NCHAR, and NVARCHAR2) into a date format.
Syntax:

In this tutorial, we are going to explain how to use Oracle TO_DATE function with basic syntax and many examples for better understanding.

How to convert varchar to date in oracle? Oracle TO_DATE function is used to convert a character string that is one of the data types (CHAR, VARCHAR2, NCHAR, and NVARCHAR2) into a date format.
Syntax:

TO_DATE(char, 'format_model', nls_language)
Enter fullscreen mode Exit fullscreen mode
  • char: The character that will be converted to a date

  • β€˜format_model’: a format that will be used to convert char to a date. If format_model is not specified, the format is DD-MON-YY., and It can be one or a combination of the following values:

Oracle Date Format

Using a date format mask, we can convert a character string to a particular date format if we use one or a combination of different masks like β€˜DD/MM’ or β€˜DD/MM/YYYY’ or β€˜DD-MON’.

Parameter Explanation
SCC or CC Century; server prefixes B.C. date with –
Years in dates YYYY or SYYYY Year; server prefixes B.C. date with –
YYY or YY or Y Last three, two, or one digit of the year
Y,YYY The year with a comma in this position
IYYY, IYY, IY, I Four-, three-, two-, or one-digit years based on the ISO standard
SYEAR or YEAR Year spelled out; server prefixes B.C. date with –
Q Quarter of year
MM Month: two-digit value
MONTH Name of month padded with blanks to length of nine characters
MON Name of month, three-letter abbreviation
RM Roman numeral month
WW or W Week of year or month
DDD or DD or D Day of the year, month, or week
DAY Name of day padded with blanks to a length of nine characters
DY Name of the day; three-letter abbreviation
J Julian day; the number of days since December 31, 4713 B.C.
RRRR Accepts a 2-digit year and returns a 4-digit year.A value between 0-49 will return a 20xx year. A value between 50-99 will return a 19xx year.
HH Hour of day (1-12).
HH12 Hour of day (1-12).
HH24 Hour of day (0-23).
MI Minute (0-59).
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
AM, A.M., PM, or P.M. Meridian indicator
  • nls_language: it’s an expression that specifies the name of the day and month and what it looks like. it’s an optional parameter; Oracle TO_DATE function will use the default language for your session.
'NLS_DATE_LANGUAGE = American'
Enter fullscreen mode Exit fullscreen mode

Oracle TO_DATE function with NLS_DATE_FORMAT :

If the client sets the NLS_* parameters β€” they override the server in all cases. In fact, if the client sets the NLS_LANG parameter β€” that causes all
NLS_* settings on the server to be ignored and the defaults for that NLS_LANG specified on the client on use. Source: Oracle

If we need to show the date in a specific format like β€œyyyymmdd”, we have to set NLS_DATE_FORMAT on the session level after connecting.

First, we can query the current value of NLS_DATE_FORMAT by executing this select statement :

SELECT *
FROM   V$NLS_PARAMETERS
WHERE  PARAMETER = 'NLS_DATE_FORMAT';
Enter fullscreen mode Exit fullscreen mode

Result
oracle to_date NLS_DATE_FORMAT-min

After that, we need to set NLS_DATE_FORMAT by :

ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YYYY';
Enter fullscreen mode Exit fullscreen mode

Examples:

Let’s take some examples about TO_DATE and how to use it:

Example (1):

SELECT to_date ('2019/03/01', 'yyyy/mm/dd')
FROM   dual;
Enter fullscreen mode Exit fullscreen mode

Result
alt text

Example (2):

SELECT TO_DATE('030119', 'MMDDYY')
FROM dual;

Result
alt text

This Article created initially on Oracle TO_DATE Function

Similar oracle functions Like Oracle TO_DATE
TO_CHAR : Convert number and date to string.
TO_NUMBER : Converts a value to a NUMBER type
In this tutorial, you have learned how to use the Oracle TO_DATE function to convert a character string to a date format by giving 4+ different examples for better understanding.
Hopefully, it was clear and concise. 
Enter fullscreen mode Exit fullscreen mode

Top comments (0)