SQL Date Functions: to_date Vs. to_char

Converting a string from one format to another is called date formatting. In SQL, there are two functions for date formatting: to_date and to_char. Here are some sample SQL queries to demonstrate how to use these functions.

IN THIS PAGE

1. Using the to_date function
   1. to_date example
2. Using the to_char function
   1. to_char example

To format a date in SQL, you can use the to_date and to_char functions. Here are some examples:

Using the to_date function

INSERT INTO emp (empno, hiredate) 
VALUES (101, to_date('13-aug-09 09:30 a.m.', 'dd-mon-yy hh:mi a.m.'));

This query inserts a record into the emp table with the employee number (empno) and hire date formatted according to the specified date format.

This query returns the current date in the format “dd/mm/yyyy”. For example, if the current date is July 4, 2009, it will return “04/07/2009”.

SELECT * FROM salary
WHERE TO_CHAR(salary_date, 'MM') = '03';

This query retrieves rows from the salary table where the salary date’s month is March.

to_date example

The to_date function you can use to format the Date column to the format you need.

insert into emp (empno,hiredate) 
values(101, to_date('dd-mon-yyhh:mi a.m.', '13-aug-09 09:30 a.m.'));

Using the to_char function

SELECT TO_CHAR(SYSDATE, 'MONTH') FROM DUAL;

This query returns the current month in the full month name format. For example, if the current month is August, it will return “August”.

SELECT TO_CHAR(SYSDATE, 'dd/mm/yyyy') FROM DUAL;

to_char example

The to_char function you can use to format the DATE or number to a character string.

You can format the DATE in many ways. The new format must be enclosed within single quotes in the SQL query.

SELECT TO_CHAR(SYSDATE,'MONTH') FROM DUAL; 

It will return in August.

SELECT TO_CHAR(SYSDATE,'dd/mm/yyyy') FROM DUAL;

It will return 04/07/2009.

SELECT * FROM SALARY 
WHERE To_CHAR(SALARY_DATE, 'MM')='03'; 

It will return rows of March salary.

References

• Mastering Oracle SQL