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.

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