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)
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'
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';
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';
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;
Result
Example (2):
SELECT TO_DATE('030119', 'MMDDYY')
FROM dual;
Result
| 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.
Top comments (0)