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.
| Id | FirstName | LastName | VisitDate |
|---|---|---|---|
| 1 | Jane | Miller | 2019-11-17 |
| 2 | Alex | Smith | 2019-11-20 |
| 3 | William | Brown | 2019-11-20 |
| 4 | Alice | Thomas | 2019-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 VisitDayFROM MedicalVisit;
Here’s the result of the query:
| FirstName | LastName | VisitDay |
|---|---|---|
| Jane | Miller | 17 |
| Alex | Smith | 20 |
| William | Brown | 20 |
| Alice | Thomas | 5 |
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’.