How to Write Common Date Functions in SQL with Examples (2024)

/ #Sql
How to Write Common Date Functions in SQL with Examples (1)
Ibrahim Abayomi Ogunbiyi
How to Write Common Date Functions in SQL with Examples (2)

When querying data from a database, you will frequently encounter the date datatype. Depending on what you want to achieve, you may need to extract subset information from the date column, perform some operation, and so on.

SQL provides a variety of date functions that can assist you with your task. In this tutorial, we will look at various common date functions in SQL and some examples to show how they work. Without further ado let's get started.

Note: There are numerous SQL flavors available, and the functions for completing a specific task may differ between flavors. This tutorial will concentrate on three of the most popular SQL flavors: PostgreSQL, MySQL, and SQL server. We will start with PostgreSQL functions and then present the variants of the other flavors if they differ from PostgreSQL.

Date Data types

Date data types are one of the built in data types in SQL that you use to store date values. A date value is usually stored across all database management systems or flavors in the timestamp format, that is YYYY-MM-DD HH:MM:SS – for example 2022-01-01 10:08:56.

Before we get started, we will be using this table we created to explain the function we will be talking about later in the article. You can create it using the following query. Note the SQL flavor we are using is PostgreSQL.

DROP TABLE IF EXISTS student;CREATE TABLE student ( student_id SERIAL PRIMARY KEY, student_name VARCHAR(30), admitted_date DATE);INSERT INTO student VALUES (11, 'Ibrahim', '2012-10-01');INSERT INTO student VALUES (7, 'Taiwo', '2013-12-01');INSERT INTO student VALUES (9, 'Nurain', '2012-11-21');INSERT INTO student VALUES (8, 'Joel', '2012-10-31');INSERT INTO student VALUES (10, 'Mustapha', '2015-11-01');INSERT INTO student VALUES (5, 'Muritadoh', '2011-09-01');INSERT INTO student VALUES (2, 'Yusuf', '2022-05-03');INSERT INTO student VALUES (3, 'Habeebah', '2012-11-01');INSERT INTO student VALUES (1, 'Tomiwa', '2013-04-01');INSERT INTO student VALUES (4, 'Gbadebo', '2008-10-01');INSERT INTO student VALUES (12, 'Tolu', '2009-11-21');SELECT * FROM student;

Common SQL Date Functions

Let's look at the common date functions you will work with on a daily basis.

How to use the Now() function

You use the Now() function to return the current timestamp (date +time) of the computer system where the database management system is currently hosted. In PostgreSQL it also includes the time zone of the timestamp as shown below.

SELECT NOW();
How to Write Common Date Functions in SQL with Examples (3)

The function for getting the current timestamp in MySQL is also the same as in PostgreSQL – Now(). But in SQL server, you use the function CURRENT_TIMESTAMP.

How to use the current_date function

This function, as the name implies, gets the current date of the computer system on which the SQL database is running. When retrieving the current date in PostgreSQL, you do not need to use a parenthesis, as you can see below:

SELECT current_date;

In MySQL, you use the CURDATE() function to get the current date, but SQLServer uses GETDATE ().

How to use the Extract() or Date_Part() functions

You use the Extract or date part functions to extract a certain part or unit of a date or date column.

Let's start with the Extract function. Its syntax looks like this:

EXTRACT(unit FROM date/date_column)

The unit part of the Extract function is a unit you can extract from a date such as DAY, WEEK , YEAR , QUARTER , and so on. Click here to see the list of units that you can extract from a date or date column in SQL.

Say for instance in the above student table we've created earlier you wish to extract the year the students were admitted from the admitted_date column you can achieve that using the EXTRACT() function as shown below.

SELECT *,EXTRACT(YEAR FROM admitted_date) As "Year of Admission"FROM student;
How to Write Common Date Functions in SQL with Examples (4)

The EXTRACT() function is only available in only PostgreSQL and MySQL and works similarly. Another Function that works like EXTRACT() is DATEPART() and it is also available in PostgreSQL and SQLServer. Let's look at how the DATEPART() function works.

The syntax for Datepart in PostgreSQL looks a little bit different from the one SQLServer uses in that it has an underscore between the date and part. You also need to pass in the unit in a single quote as shown below:

SELECT DATE_PART('Year', admitted_date)FROM student;
How to Write Common Date Functions in SQL with Examples (5)

For SQLServer there won't be any underscore between the date and part, and the unit will not be enclosed in single quotes. For example the above result can be generated in SQLServer as shown below.

SELECT DATEPART(YEAR, admitted_date)FROM student;

How to add intervals or parts to dates

Intervals are units that you can add to a date – for example a days interval, time interval, and so on.

For example, say you want to add 1 day interval to all the dates in a particular table. In PostgreSQL there is no dedicated function you can use to add an interval to a particular date. Instead, you can do this using arithmetic operations.

The syntax for achieving that is shown below:

SELECT date/date_column + INTERVAL "# unit"

Where # is an integer such as 3, 4, and so on, and unit can be Days, Year, and so on. Click here for a list of units that can be passed as an interval.

Say, for instance, that you want to add an interval of 3 days to the admitted_date column in the student table. You can do this in PostgreSQL using the following query:

SELECT *,admitted_date + INTERVAL '3 Days' AS "3_daysadded"FROM student;
How to Write Common Date Functions in SQL with Examples (6)

Now that you've seen how to add intervals to dates in PostgreSQL, let's see how it is done in MySQL and SQLServer. In MySQL and SQLServer there are functions that you can use to add intervals to dates.

In my SQL, the name of the function is called DATE_ADD() and the syntax is shown below:

DATE_ADD(date/date_column, INTERVAL value unit)

For example, you can get the above table using MySQL by typing the following code:

SELECT *,DATE_ADD(admitted_date, INTERVAL 3 DAY) AS "3_daysadded"FROM student;

In SQLServer, the function you use is similar to the one in MySQL but with a small difference. The syntax for the function used is shown below:

DATEADD (datepart/unit , number , date/date_column) 

You can replicate the above table in SQLServer like this:

SELECT *,DATEADD (day , 3 , admitted_date) AS "3_daysadded"FROM student;

How to subtract intervals from dates

Subtracting intervals from dates in PostgreSQL works like adding intervals, except that the operator changes from plus to minus. For example, say you want to subtract 3 days from the admitted_date column. You can do this using the below code:

SELECT *,admitted_date - INTERVAL '3 Days' AS "3_dayssubtracted"FROM student;
How to Write Common Date Functions in SQL with Examples (7)

In MySQL, you use the DATESUB function to subtract intervals from the date. You can replicate the above table in MySQL using the following query:

SELECT *,DATE_SUB(admitted_date, INTERVAL 3 DAY) AS "3_dayssubtracted"FROM student;

In SQLServer, you still use the DATEADD function, but instead of specifying a positive value in the function parameter, you use a negative value. It looks like this:

SELECT *,DATEADD (day , -3 , admitted_date) AS "3_dayssubtracted"FROM student;

How to subtract two dates

To subtract two dates in PostgreSQL, there is also not a dedicated function. But you can use arithmetic operators to achieve your desired result.

SELECT '2012-10-31'::date -'2012-05-01'::date AS days;
How to Write Common Date Functions in SQL with Examples (8)

In MySQL, there is a function called DATE_DIFF() that you can use to achieve this, while for SQLServer you use the DATEDIFF() function. Click here to learn more about it.

Conclusion

In this tutorial you've learned some common date functions you will use with when working with dates in SQL.

You learned how to get the current timestamp, get the current date, extract parts from a date, and how to add or subtract dates. Also you learned how each date function differs across different SQL flavors.

Thank you for reading. You can check out the below resources to learn more about date function across the three different SQL flavor discussed in this article.

  1. Microsoft SQL database functions
  2. Postgres date/time functions and operators
  3. MySQL date and time functions reference manual

ADVERTIsem*nT

ADVERTIsem*nT

ADVERTIsem*nT

ADVERTIsem*nT

ADVERTIsem*nT

ADVERTIsem*nT

ADVERTIsem*nT

ADVERTIsem*nT

How to Write Common Date Functions in SQL with Examples (9)
Ibrahim Abayomi Ogunbiyi

I'm an ardent data scientist who loves breaking and fixing up stuff related to data, and clearly taking care of the data to give me insights as to what it is portraying.

If you read this far, thank the author to show them you care.

Learn to code for free. freeCodeCamp's open source curriculum has helped more than 40,000 people get jobs as developers. Get started

ADVERTIsem*nT

I am an experienced data professional with a deep understanding of SQL and its various flavors, including PostgreSQL, MySQL, and SQL Server. My expertise is demonstrated through years of practical experience and a comprehensive knowledge of database management systems. I have successfully utilized SQL to query and manipulate data, and I'm well-versed in the nuances of different SQL flavors.

Now, let's delve into the concepts covered in the article by Ibrahim Abayomi Ogunbiyi, which discusses common date functions in SQL.

Date Data Types

Date data types are essential built-in data types in SQL used to store date values. Typically, date values are stored in the timestamp format, such as YYYY-MM-DD HH:MM:SS.

Common SQL Date Functions

1. Now() Function:

  • PostgreSQL and MySQL both use the NOW() function to return the current timestamp.
  • SQL Server utilizes the CURRENT_TIMESTAMP function for the same purpose.

    Example:

    -- PostgreSQL and MySQL
    SELECT NOW();
    
    -- SQL Server
    SELECT CURRENT_TIMESTAMP;

2. current_date Function:

  • PostgreSQL uses CURRENT_DATE to retrieve the current date.
  • MySQL employs CURDATE().
  • SQL Server utilizes GETDATE().

    Example:

    -- PostgreSQL
    SELECT CURRENT_DATE;
    
    -- MySQL
    SELECT CURDATE();
    
    -- SQL Server
    SELECT GETDATE();

3. Extract() or Date_Part() Functions:

  • Extract function is available in PostgreSQL and MySQL.
  • SQL Server uses DATEPART() function.

    Example:

    -- PostgreSQL and MySQL
    SELECT *, EXTRACT(YEAR FROM admitted_date) AS "Year of Admission" FROM student;
    
    -- SQL Server
    SELECT DATEPART(YEAR, admitted_date) FROM student;

4. Adding Intervals to Dates:

  • PostgreSQL uses arithmetic operations.
  • MySQL uses DATE_ADD().
  • SQL Server uses DATEADD().

    Example (PostgreSQL):

    SELECT *, admitted_date + INTERVAL '3 Days' AS "3_daysadded" FROM student;

    Example (MySQL):

    SELECT *, DATE_ADD(admitted_date, INTERVAL 3 DAY) AS "3_daysadded" FROM student;

    Example (SQL Server):

    SELECT *, DATEADD(day, 3, admitted_date) AS "3_daysadded" FROM student;

5. Subtracting Intervals from Dates:

  • PostgreSQL uses the minus operator.
  • MySQL uses DATE_SUB().
  • SQL Server uses negative values with DATEADD().

    Example (PostgreSQL):

    SELECT *, admitted_date - INTERVAL '3 Days' AS "3_dayssubtracted" FROM student;

    Example (MySQL):

    SELECT *, DATE_SUB(admitted_date, INTERVAL 3 DAY) AS "3_dayssubtracted" FROM student;

    Example (SQL Server):

    SELECT *, DATEADD(day, -3, admitted_date) AS "3_dayssubtracted" FROM student;

6. Subtracting Two Dates:

  • PostgreSQL uses arithmetic operators.
  • MySQL uses DATEDIFF().
  • SQL Server uses DATEDIFF().

    Example (PostgreSQL):

    SELECT '2012-10-31'::date - '2012-05-01'::date AS days;

    Example (MySQL):

    SELECT DATEDIFF('2012-10-31', '2012-05-01') AS days;

    Example (SQL Server):

    SELECT DATEDIFF(day, '2012-05-01', '2012-10-31') AS days;

In conclusion, this tutorial provides a comprehensive overview of common date functions in SQL, covering various tasks such as retrieving current timestamps, extracting parts from dates, and manipulating date intervals. The nuances between PostgreSQL, MySQL, and SQL Server are highlighted to ensure a broad understanding of date functions across different SQL flavors.

How to Write Common Date Functions in SQL with Examples (2024)
Top Articles
Latest Posts
Article information

Author: Gregorio Kreiger

Last Updated:

Views: 6150

Rating: 4.7 / 5 (77 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Gregorio Kreiger

Birthday: 1994-12-18

Address: 89212 Tracey Ramp, Sunside, MT 08453-0951

Phone: +9014805370218

Job: Customer Designer

Hobby: Mountain biking, Orienteering, Hiking, Sewing, Backpacking, Mushroom hunting, Backpacking

Introduction: My name is Gregorio Kreiger, I am a tender, brainy, enthusiastic, combative, agreeable, gentle, gentle person who loves writing and wants to share my knowledge and understanding with you.