How to Extract Day from Date in SQL

Summary: in this tutorial, you will learn how to extract the day of the month from a date in SQL.

To extract the day of the month from a specified date, you use the EXTRACT() function. The following illustrates the syntax:

EXTRACT(DAY FROM date)
Code language: SQL (Structured Query Language) (sql)

In this syntax, you pass the date from which you want to extract the day of the month to the EXTRACT() function. The date can be any valid date literal or an expression that evaluates to a date value.

The EXTRACT() function returns a number that represents the day of the month of the input date.

The following example shows how to extract the day of the month from the date of August 21st 2018:

SELECT EXTRACT(MONTH FROM '2018-08-21')
Code language: SQL (Structured Query Language) (sql)

The statement issued the following output:

21
Code language: SQL (Structured Query Language) (sql)

To get the day of the current date, you use the CURRENT_TIMESTAMP function in the EXTRACT() function as follows:

SELECT EXTRACT(DAY FROM CURRENT_TIMESTAMP)
Code language: SQL (Structured Query Language) (sql)

Note that the EXTRACT() function is a SQL standard function supported by MySQL, Oracle, and PostgreSQL.

SQL Server does not support the EXTRACT() function. If you use SQL Server, you can use the DAY() or DATEPART() function instead to extract the day of the month from a date.

For example, both statements below return the day of the current date in SQL Server:

SELECT DAY(CURRENT_TIMESTAMP);

SELECT DATEPART(DAY, CURRENT_TIMESTAMP);
Code language: SQL (Structured Query Language) (sql)

Besides providing the EXTRACT() function, MySQL supports the DAY() function to return the day of the month from a date.

SQLite does not support EXTRACT(), DAY() or DATEPART() function. To extract a month from a date, you can use the strftime() function as follows:

SELECT strftime('%d', CURRENT_TIMESTAMP)
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use various functions to extract the day of the month from a date in SQL.

Was this tutorial helpful ?