How to Get the Day from a Date in T-SQL

Problem

You’d like to get the number of day from a date field in a SQL Server database. For example, from the date of February 27, 2023, you'd like to get the number 27.

Example

Our database has a table named MedicalVisit with data in the columns Id, FirstName, LastName, and VisitDate.

For each patient, let’s find the day of the medical visit. We’ll get it from the VisitDate field.

Solution

We’ll use the DAY() function. Here’s the query you would write:

SELECT 
  FirstName,
  LastNme,
  DAY(VisitDate) AS VisitDay
FROM MedicalVisit;

Here’s the result of the query:

Discussion

If you want to get the number of day from a date in a table, use the SQL Server DAY() function. This function takes only one argument – the date. This can be a date or date and time data type. (In our example, the column VisitDate is of the date data type.) The argument can be a column name or an expression. (In our example, it is the VisitDate column.)

DAY() returns the number of the day of the month as an integer from 1 to 31. For Alice Thomas’ visit, the day is 5; we get it from the YYYY-MM-DD date 2019-11-05.