SQL DAY() Function


The SQL DAY() is a function, and return or extract the day value portion or day of the month (a whole integer number from 1 to 31) of a given input date value.

The SQL DAY() function is supports only date or datetime based table columns or fields.

It can be used in SELECT statement as well in where clause.

You can also search these topics using sql last day of month, sql server date today, sql server day function, last day of month sql, sql days between dates, sql day of the week, get day from date sql, sql where date today, sql today function.

SQL DAY() Syntax

The below syntax is used to get day value portion from a given input date value using DAY() function.

For SQL SERVER / MY SQL / MS ACCESS


SELECT DAY('valid_date_value');

The below syntax is used to get day of the month (a integer number from 1 to 31) value from a given table date or datetime column using DAY() function.

For MS ACCESS / SQL SERVER / MY SQL


SELECT DAY(date_column_name1) FROM table_name;


SQL DAY() Example - Using Expression Or Formula

The following SQL SELECT statement will extract the day of the month value from a given input date value. We use now() function to get the input date value.


SELECT 
NOW() AS 'Current Date and Time', 
DAY(NOW()) AS 'Day Value';

The result of above query is:

Current Date and Time Day Value
24-08-2012 19:37:30 24

Sample Database Table - BookOrder

BookID BookName OrderDate Qty DeliveryDate
1 SQL: The Complete Reference 13-05-2000 10:29:29 10 27-05-2000 09:54:59
2 The Gurus Guide To SQL Server 17-06-2002 23:05:18 4 21-07-2002 07:48:21
3 Troubleshooting Oracle 19-07-2007 13:59:49 13 28-07-2007 06:53:51
4 MySql for professionals 24-10-2000 19:31:33 2 27-10-2000 23:31:55

SQL DAY() Example - With Table Column

The following SQL SELECT statement display the column "OrderDate" and "DeliveryDate", from the "BookOrder" table. The DAY() function will extract the day of the month value(a whole integer number from 1 to 31) from the column "OrderDate" and we will stored that value to a new column called "Order Day" and "Delivery Day".


SELECT 
OrderDate, DAY(OrderDate) As 'Order Day', 
DeliveryDate, DAY(DeliveryDate) As 'Delivery Day' 
FROM BookOrder;

The result of above query is:

OrderDate Order Day DeliveryDate Delivery Day
13-05-2000 10:29:29 13 27-05-2000 09:54:59 27
17-06-2002 23:05:18 17 21-07-2002 07:48:21 21
19-07-2007 13:59:49 19 28-07-2007 06:53:51 28
24-10-2000 19:31:33 24 27-10-2000 23:31:55 27
You can also search these topics using add days to date sql, get day from date sql server, sql server date functions, sql date functions, ms sql date functions, mssql current date, sql server convert date, sql server date time, sql subtract dates, sql server compare dates, sql date operations.