---
title: "SQL Date Functions: to_date Vs. to_char"
description: "SQL date formatting can be done using \"to_date\" and \"to_char\" functions, allowing various custom outputs."
url: https://srinimf.com/2023/12/03/sql-date-conversion-functions-all-you-need/
date: 2023-12-03
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2022/06/pexels-photo-3791185.jpeg?fit=1880%2C1253&ssl=1
categories: ["SQL"]
tags: ["SQL Date Conversion Functions"]
type: post
lang: en
---

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

*SQL Date format*

**IN THIS PAGE**

1. [Using the to_date function](https://srinimf.com/2023/12/03/sql-date-conversion-functions-all-you-need/#using-the-to-date-function)
   1. [to_date example](https://srinimf.com/2023/12/03/sql-date-conversion-functions-all-you-need/#to-date-example)
2. [Using the to_char function](https://srinimf.com/2023/12/03/sql-date-conversion-functions-all-you-need/#using-the-to-char-function)
   1. [to_char example](https://srinimf.com/2023/12/03/sql-date-conversion-functions-all-you-need/#to-char-example)

SQL Date Functions

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](https://srinimf.com/2016/09/02/sql-query-best-way-of-retrieving-salary-and-hiredate/).

```
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](https://www.amazon.in/Mastering-Oracle-SQL-Putting-Work-ebook/dp/B0026OR3LC?tag=srinimf-21)

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2023%2F12%2F03%2Fsql-date-conversion-functions-all-you-need%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2022%2F06%2Fpexels-photo-3791185.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=SQL%20Date%20Functions%3A%20to_date%20Vs.%20to_char)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2023/12/03/sql-date-conversion-functions-all-you-need/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2023/12/03/sql-date-conversion-functions-all-you-need/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2023/12/03/sql-date-conversion-functions-all-you-need/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2023/12/03/sql-date-conversion-functions-all-you-need/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20SQL%20Date%20Functions%3A%20to_date%20Vs.%20to_char&body=https%3A%2F%2Fsrinimf.com%2F2023%2F12%2F03%2Fsql-date-conversion-functions-all-you-need%2F&share=email)
-

### Like this:

Like Loading…