2 Exclusive SQL Functions you NEED to Convert Date Format

While working with dates in SQL, you may come across various questions. For those, these two functions will address you.

Those are to_date and to_char. Each function has its unique purpose. Below are the best examples for you.

Date conversion

To_date

Formatted date you can insert in SQL query. For that you can use the to_date function; with it, the time can also be inserted.

Assume emp (empno, ename, hiredate)

SQL Query

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

To_char

Converting date or number to a character string you can do it with the to_ char function.

To convert date:

With the TO_CHAR function, a date item can be formatted in many ways. The format must be enclosed within single quotes. Consider, 19-AUG-96 as the system date, then,

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

It will return August.

SQL>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.

Recent posts

Author: Srini

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