Oracle TO_DATE Function - Complete Guide
Tags: sql, database, oracle, programming
Published: 2022-07-16 00:03:52

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
![alt text](https://www.oraask.com/wp-content/uploads/2019/03/Oracle-to_date-function-example-1.jpg "Example (1)")


### Example (2):

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

Result
![alt text](https://www.oraask.com/wp-content/uploads/2019/03/Oracle-to_date-function-example-2.jpg "Example (2)")

|Similar oracle functions Like Oracle TO_DATE|
|--------------------------------------------|
|[TO_CHAR](https://www.oraask.com/wiki/oracle-to-char) : Convert number and date to string.|
|[TO_NUMBER](https://www.oraask.com/wiki/oracle-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.