Overview
The DAY function returns the day part of the date as an integer from the date or datetime provided.
Explanation
Syntax
DAY(date)Parameters
- 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 with NULL values
If the parameter of the DAY function is NULL, a NULL value will be returned.
SELECT DAY(NULL) as day
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 dayThe 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 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]
Additional Information
- Mimic timestamp behavior of other database platforms to store last modified date
- SQL Convert Date to YYYYMMDD
- SQL Server DIFFERENCE Function
- SQL Server CONCAT Function
Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.
- MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018
