DAY Function in SQL Server

by

In this SQL Server tutorial, you will learn how to use the DAY function in SQL Server, where you will work on the day part of the given datetime value.

You will learn how to use the DAY() function with only literal date and on the table columns; then, you will learn where to use the DAY() function in real life with examples.

DAY Function in SQL Server

The DAY() function in SQL Server extracts the day of the given date and time expression. It is helpful when you must analyse data based on the day of the month. So, this function only extracts and returns the day part of the specified date and time value.

This syntax is given below.

DAY( expression)

Where,

  • DAY(): The function is to get the date part from the specified datatime value.
  • expression: It can be the value or datetime value, column, user-defined variable and string literal from which you want to extract the day part. The value can be of these data types such as date, datetime, datetime2, datetimeoffset, smalltime, and time.

The return type of the DAY() function is int, which means when you pass the datetime value for extracting the day of value. Then, the DAY() function returns the day, and the data type of that day value is an integer.

  • Remember, if you pass the datetime value that contains only the time part, then, in that case, the DAY() function returns the 1.

Now, let’s see with an example how the DAY() function returns the day of the month based on the given literal date value.

SELECT DAY('2023-05-23') AS DayOfDate;
DAY Function in SQL Server

From the output, the DAY() function returned the result as 23, which is the day part of the specified date ‘2023-05-23’. You can see how easy it is to use this function. You only need to provide the datetime value, which returns the day from that datetime value.

This is how you can get the day part from the literal date value using the DAY function in SQL Server.

DAY Function in SQL Server with Time Part Only

You have applied the DAY() function on the date only; now, you will see how to use the DAY() function with the time part. This means you will pass the time value to the DAY() function that contains only the time part; see how the DAY() function works.

For example, you have the time value as ’12:34:04′, so use the query below to see how the DAY() function only works with the time part.

SELECT DAY('12:34:04') AS DayFromTime;
DAY Function in SQL Server with Time Part Only

The DAY() function returns the value as 1 for the time part. Remember, when you try to find the day of the given time part only, it always returns 1.

This is how to pass the time value to the DAY function in SQL Server.

DAY Function in SQL Server on Table Columns

Until now, you have used the DAY() function with a literal date value; here, you will see how to use the DAY() function with the table’s column.

For example, you have a Sales table with columns sale_id, product_name, sale_date, and sale_amount, as shown below.

DAY Function in SQL Server on Sales Table Columns

Here, you have a task to group sales data by the day of the month using the above table. Use the below query and see how the DAY() function is used here.

SELECT DAY(sale_date) AS DayOfMonth, SUM(sale_amount) AS TotalSales
FROM Sales
GROUP BY DAY(sale_date);
DAY Function in SQL Server on Table Columns

From the above query output, the result set contains the total sales for each day of the month. For example, the total sales for the 5th day of the month is 498000 dollars, and for the 15th day is 620200 dollars.

Understand the query part DAY(sale_date). Here, the DAY() function extracts the day part of the sale_date column.

Then GROUP BY DAY(sale_date) groups sales data by day. Here, it groups the records based on the day part of the SaleDate.

Then, the query part SUM(sale_amount) in the SELECT clause operates on these groups and computes the total sales using the SUM() for each distinct day of the month.

After that, the result is returned containing two columns, DayOfMonth and TotalSales, as shown in the above picture which shows the total sales for each day of the month.

You can use the DAY function in SQL Server with a table column to extract the day part of the datetime value in the column.

  • Remember, you can use the DAY() function for different purposes, such as analyzing sales patterns and plan-making strategies.
  • In banking, you can use the DAY() function to calculate the interest based on daily transactions. Also, tracking patient admissions or discharges per day in healthcare is essential.

Conclusion

In this SQL Server tutorial, you learned how to extract the day part of the given date using the DAY() function, and with an example, you extracted the day of the specified date. You also applied the DAY() function on the table columns to compute the total sales on each day of the month, and finally, practical application of the DAY() function.

You may like to read: