Oracle TO_DATE Function - Complete Guide

Hassan Abd Elrahman — Sat, 16 Jul 2022 00:03:52 +0000

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.

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.

Run Oracle SQL Script From Command Line in Windows

Hassan Abd Elrahman — Fri, 15 Jul 2022 06:57:52 +0000

Sometimes we need to run a particular SQL script from the Windows command line “CMD” with or without parameters.

This article will guide you through the steps required to perform
this task to get an employee’s information — both with and without
parameters.
To Run SQL Scripts from Windows Command Line CMD

Press Windows + R from your keyboard, then type cmd and press enter.

Change the current directory to the directory where your script is available.

To run SQL Script without parameters, we need to type this command.

sqlplus hr/hr @ScriptWithoutParameter.sql

To run SQL Script with parameters, we need to type this command.

sqlplus hr/hr @ScriptWithoutParameter.sql 101

It is important that when running SQL script with parameters from CMD:

In our script, the WHERE condition should be typed in the substitution variable as follows

select * from EMPLOYEES where employee_id = &1;

For the script above, we passed &1 to be replaced with 101 to bring employee number 101's data.

To Run SQL Script from SQL PLUS

Open SQL PLUS and then connect to the database.
In SQL Command, type the following command and press enter

:\MyScript\ScriptWithoutParameter.sql

This Article created initially on Oraask.com

DEV Community: Hassan Abd Elrahman

Oracle TO_DATE Function - Complete Guide

Examples:

Example (1):

Example (2):

Run Oracle SQL Script From Command Line in Windows