SQL Server DAY Function


The DAY function returns the day part of the date as an integer from the date or datetime provided.




  • Date – Is the date or datetime provided.

Simple DAY Example

The following example will show the day for March 9, 2021.

SELECT DAY('3-9-2021 5:00:55 PM') as day
DAY function

DAY function with NULL values

If the parameter of the DAY function is NULL, a NULL value will be returned.

day function with null values

Conversion Failed Error for DAY Function

A typical error will occur if the date is invalid. The following example is using a wrong day number (32).

SELECT DAY('3-32-2021 5:00:55 PM') as day

The error message displayed is:

Conversion failed when converting date and/or time from character string.

Using DAY Function with just Time

If the DAY function receives just a time value, the value returned is 1.

SELECT DAY('5:00:55 PM') as day
day with time values

DAY Function with Data from a Table

The following example will show the day for the StartDate.

SELECT StartDate, DAY(StartDate) as day
FROM [Production].[ProductCostHistory]
day function with tables

Related Articles

Comments For This Article

get free sql tips
agree to terms