SQL Date Functions: A Detailed Guide

By Community / Use Cases
Apr 14, 2023

SQL provides a date type that developers can use to store date values. Also, to make working with dates easier, SQL has several date functions for retrieving, formatting, and manipulating dates.

In this post, you’ll learn about some SQL date functions and how to use them.

Why date manipulation is important in SQL

Before we look at the various SQL date functions, let’s see some of the reasons why you might want to manipulate dates in SQL.

Dates are data and after processing, they can become clearer information. Take for example the date 20/02/2023. You can process it such that it reads as the 20th day of February 2023 which is clearer and more readable for us humans. Also, you may process the above date data to find out what day of the week the 20th was.

With date manipulation in SQL, you can change the format of the above date to meet a specific requirement. For example, you can display the month before the day or show the full month name instead of an integer.

You can also manipulate the date by extracting only certain parts of it, like the year, the month, or the day.

Sometimes, you may need to calculate a future date or a past date, relative to a specific date. That is, you might want SQL to tell you what the date will be one month from the current date. Similarly, you can use an SQL date function to manipulate the current date such that it returns the date one month ago. To manipulate the current date, you’ll add or subtract the required number of days.

This ability to manipulate dates lets you extend the functions your SQL application performs without depending on another tool or programming language.

How do you write a date in SQL?

The simple answer to this question is that you create a date-time type column in your table.

SQL has multiple formats for writing dates and you can use one or more date functions to determine the correct format for the type you need. For example, the current date function in MySQL returns dates in the following format by default:

Year-Month-Day

Following the above format, a valid date will look like 2023-02-28.

Examples of SQL date functions

In this section, we’ll discuss some SQL date functions and how to use them. It’s worth mentioning that SQL date functions vary slightly from one SQL distribution to another. For example, the syntax and behavior of date functions may differ between MySQL and SQL Server; let’s look at a few functions in each.

1. CURDATE()

The CURDATE() date function in MySQL returns the current date. Assuming today is the first day of March, it would return today’s date in the following format: 2023-03-01.

How to use the CURDATE() Function

To get the current date in MySQL, simply run the following query:

SELECT CURDATE();

This code always returns the current date.

A more practical usage of the CURDATE() function is in a WHERE clause. For example, you can use it to fetch all the rows from a table with a date value equal to the current date. The following query shows how to do that:

SELECT * from goods WHERE expiry_date = CURDATE();

2. EXTRACT()

Sometimes you might want to manipulate date data such that you’re left with only one part of the date. The EXTRACT() function does exactly that in MySQL — you can use it to extract the month, day, or year.

How to use the EXTRACT() Function

To extract only the month from a date, run the following query:

SELECT EXTRACT(MONTH FROM '2020-12-02');

Running this query returns 12, which represents the month of December.

3. DAY(), MONTH(), and YEAR()

In MySQL, the DAY(), MONTH(), and YEAR() functions extract only the day, month, or year, respectively, from a date. You can see that these functions are more specific alternatives of the extract function from the last example

How to use Day(), Month(), and Year()

The following queries show how to use the DAY(), MONTH(), and YEAR() functions:

SELECT DAY('2020-12-02');

SELECT MONTH('2020-12-02');

SELECT YEAR('2020-12-02');

4. DATE_ADD()

The DATE_ADD() function in MySQL lets you specify a date and the interval you wish to add to it. It then returns the full date at that interval. The interval could be DAY, MONTH, YEAR, or even a time value, like hours or minutes.

How to use DATE_ADD()

To add five days to the current day, run the following query:

SELECT DATE_ADD(CURDATE(), INTERVAL 5 DAY);

If the current date at the time of execution is March 1, the above query returns 2023-03-06.

5. DATE_SUB()

Just like its name suggests, DATE_SUB() subtracts the designated interval from the date input. MySQL’s DATE_SUB() is the inverse of DATE_ADD().

How to use DATE_SUB()

Run the following query to get a date five days earlier than the current date:

SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY);

6. DATEDIFF()

You can subtract two dates in MySQL using the DATEDIFF() function. You provide the dates as arguments inside the function and it returns the difference. The return can be a positive or negative number depending on the arguments.

How to use DATEDIFF()

Let’s calculate the difference between today and last Christmas. To do that, run the following query:

SELECT DATEDIFF(CURDATE(), '2022-12-25');

Running the above query should return the number of days that have passed since Christmas.

7. DATE_FORMAT()

This function lets you manipulate the format for a date in MySQL. It requires the date and format strings as arguments. The format string uses specifiers to determine how it displays the day, month, and year. The following table shows some date specifiers :

How to format a date in MySQL

To format a date, run the DATE_FORMAT() function like this:

SELECT DATE_FORMAT(CURDATE(), '%D %b, %Y');

Based on what’s in the specifier table, this code displays 1st Mar, 2023. With that, we’ve managed to change the default date format returned by the current date function and reformatted it as a more readable date, in English.

8. GETDATE()

In SQL Server, the GETDATE() function returns the current date and time. This function works very similarly to the CURDATE() function in MySQL, except that it also includes time in the output.

You can use GETDATE() by simply running the following query:

SELECT GETDATE();

9. DATEADD()

You can use the DATEADD() function to add or subtract a date interval from a date in SQL Server. It does the same job as the MySQL DATE_ADD() and DATE_SUB() functions. You specify subtraction by adding a negative sign to the interval integer

The following query shows how to use this function to subtract date:

DATEADD(day, -5, '2023-03-01')

10. CONVERT()

The CONVERT() function is SQL Server’s alternative for DATE_FORMAT(). Hence, it lets you specify the format for your date output.

What is a time series database?

A time series database (or TSDB) is a special type of database for storing data collected over a period of time. For example, it may have data about temperature readings at multiple, specific times of the day. This type of database is good for storing large data sets like logs.

A TSDB usually stores data as a pair consisting of a time and a value (for example, the temperature). Thus, if you plot data from a time series database as a graph, one axis represents time and the other represents the measured value (e.g, temperature).

In cases where you have a large collection of values and associated timestamps, you may want to consider using a time series database in place of a regular SQL table with a date-time column.

Summing everything up

In this post, we’ve provided a detailed guide on SQL date functions and you learned about several SQL functions that manipulate dates in ways that solve problems.

We covered ten SQL date functions. SQL date functions may vary between different SQL distributions, like MySQL and SQL Server. The first seven examples we described in this post are date functions for MySQL, while the rest are specific to SQL Server.

About the author:

This post was written by Pius Aboyi. Pius is a mobile and web developer with over 4 years of experience building for the Android platform. He writes code in Java, Kotlin, and PHP. He loves writing about tech and creating how-to tutorials for developers.