How to Convert Date Format in SQL

Converting a string from one format to other is called Date formatting. Here are two functions to convert DATE format VIZ: to_date and to_char. Below, you’ll find sample SQL queries on how to use to_date and to_char.

SQL Date format

IN THIS PAGE

  1. Date format in SQL
    1. SQL To_date
    2. SQL To_char

Date format in SQL

SQL To_date

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.'));

SQL To_char

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 enclose within single quotes in the SQL query.

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

It will return 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.

Related

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.