PL/SQL To_date

One of the important functions in PL/SQL is the TO_DATE function, which converts a character string representation of a date and time to an actual date value.


The syntax of the TO_DATE function is as follows:

TO_DATE(char, fmt, [nlsparam])


char: A string or character expression that represents a date or timestamp.
fmt: The format mask that specifies the format of the date in the char parameter. It is a string value enclosed in single quotes.
nlsparam (optional): A national language support parameter that specifies the language used to interpret the char parameter. If not specified, it defaults to the language of the current session.

The fmt parameter consists of various format elements that represent different parts of the date and time. Some of the commonly used format elements are:

YYYY: Four-digit year
MM: Two-digit month (01-12)
DD: Two-digit day of the month (01-31)
HH24: Hour of the day in 24-hour format (00-23)
MI: Minute (00-59)
SS: Second (00-59)


Here is an example of using the TO_DATE function in PL/SQL to convert a string value into a date value:

  my_date DATE;
  my_date := TO_DATE('2023-05-09 14:30:00', 'YYYY-MM-DD HH24:MI:SS');
  DBMS_OUTPUT.PUT_LINE('Date is: ' || my_date);

In this example, we are converting the string value ‘2023-05-09 14:30:00’ into a date value using the TO_DATE function. The format mask ‘YYYY-MM-DD HH24:MI:SS’ specifies the format of the date in the char parameter. The resulting date value is stored in the variable my_date and printed to the console using the DBMS_OUTPUT.PUT_LINE procedure.

Examples of how to use TO_DATE

The TO_DATE function converts string datatype to a value of DATE datatype.

to_date('January 21, 2014, 09:00 P.M.', 'Month dd, YYYY, HH:MI P.M.') 
from dual;
Result: 21-JAN-2014 21:00:00

select to_date(sysdate, 'dd-mm-yyyy HH24:MI:SS') from dual;	
Result: 26-DEC-2014 13:31:02

select to_date('062614', 'MMDDYY') from dual;	
Result: 26-JUN-2014 00:00:00

select to_date('01/02/2014', 'dd/mm/yyyy') from dual;	
Result: 01-FEB-2014 00:00:00

select to_date('21032015142309', 'ddmmyyyyhh24miss') from dual;
Result: 21-MAR-2015 14:23:09