SQL Server DAY() Function

In SQL Server, the DAY() function returns the DAY part of the given date as an integer.

Syntax:

DAY(date)

Parameters

date: An expression that resolves to one of the data types: date, datetime, datetime2, time, smalldatetime, or datetimeoffset. It can be a string literal, a user-defined variable, or a table column.

Return Value

The DAY() function returns an integer value which is the day part of the specified date. If the date contains only the time part, DAY will return 1.

Get a Day of Date as Number

In the following example, the DAY() function returns a day string of the specified datetime value.

Example: DAY()
SELECT DAY('02/17/2022 10:30:29') AS DayOfMonth

In the following example, the DAY() function is used to get the day part of the HireDate column of the Employee table.

Example: DAY() with Column
SELECT EmployeeID, FirstName, HireDate, DAY(HireDate) as Hireday FROM Employee

The DAY() function cannot be used with the time value. It always returns 1 if the time value is passed.

Example: DAY() with Time
SELECT DAY('10:30:29') AS DayOfMonth