Articles Cookbook
Back to list
SQL Server

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

Database:

Operators:

DAY()

Problem:

You’d like to get the day from a date field in a SQL Server database.

Example:

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

IdFirstNameLastNameVisitDate
1JaneMiller2019-11-17
2AlexSmith2019-11-20
3WilliamBrown2019-11-20
4AliceThomas2019-11-05

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 First_name,
       Last_name,
       DAY(VisitDate) AS VisitDay
FROM MedicalVisit;

Here’s the result of the query:

FirstNameLastNameVisitDay
JaneMiller17
AlexSmith20
WilliamBrown20
AliceThomas5

Discussion:

If you want to get a 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’.

Recommended courses:

Recommended articles:

See also:

go to top