SQL DAY Function

DAY returns the day of the month from the specified date.

The returned value is between 1 and 31.

Example

#

This example return the day of the month for the specified date.

SELECT DAY('12-18-2022 11:54:07') AS Day
Result:  1 record
Day
18

Using DAY

A common use is to get the day from the current date.

SELECT DAY(GETDATE()) as 'Current Day'
Result:  1 record
Current Day
7

Syntax

Syntax of the DAY function .

DAY(input_date) 

input_date -- a date or datetime value.

Note: DAY returns the same value as DATEPART(day, date).


More Examples

DAY in WHERE

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List all orders placed on Christmas day.
SELECT FirstName, LastName, 
       CONVERT(DATE, OrderDate) AS Date,
       OrderNumber, TotalAmount
  FROM [Order] O
  JOIN Customer C ON C.Id = O.CustomerId
 WHERE DAY(OrderDate) = 25 AND 
       MONTH(OrderDate) = 12
Result:  4 records
FirstName LastName Date OrderNumber TotalAmount
Jose Pavarotti 2012-12-25 542523 3302.60
Yoshi Latimer 2012-12-25 542524 442.00
Carlos Hernández 2013-12-25 542926 2878.08
Sven Ottlieb 2013-12-25 542927 420.00

DAY in GROUP BY, ORDER BY

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List daily total sales for September 2013.
SELECT DAY(OrderDate) AS Day,
       SUM(TotalAmount) AS 'Total Sales'
  FROM [Order]
 WHERE YEAR(OrderDate) = 2013 AND MONTH(OrderDate) = 9
 GROUP BY DAY(OrderDate)
 ORDER BY DAY(OrderDate)
Result:  22 records
Day Total Sales
1 862.18
2 1872.20
3 193.00
4 5042.95
5 5959.60

You may also like



Guides


vsn 3.1