Sql Server Datepart

How to Convert Datepart Number to Day Name in SQL Server

If you have ever worked with SQL Server, you likely encountered situations where you needed to extract certain parts of a date for easier understanding and usability. One common requirement is to convert the numeric representation of a day—obtained from a datepart function—into its corresponding name (such as “Monday” or “Tuesday”). In this blog post, we will explore how to quickly achieve this conversion in SQL Server.

Understanding the Problem

When you use the SQL Server function datepart, retrieving the day of the week from a date will give you a number between 1 and 7, where:

  • 1 = Sunday
  • 2 = Monday
  • 3 = Tuesday
  • 4 = Wednesday
  • 5 = Thursday
  • 6 = Friday
  • 7 = Saturday

For instance, executing the following query returns the day of the week as a number:

SELECT DATEPART(dw, GETDATE());

While this numeric value can be useful, it isn’t very user-friendly; our goal is to convert this numeric output into a more readable format, specifically the name of the day.

The Solution: Using datename

To get the name of the day instead of a number, you can utilize the DATENAME function in SQL Server. The DATENAME function allows you to return the name of a specific date part for a specified date. Here’s how you can do it:

Step 1: Using DATENAME Function

You can simply replace the datepart function with datename in your SQL query as follows:

SELECT DATENAME(weekday, GETDATE());

Explanation of the Query:

  • DATENAME: This function takes two parameters; the first is the date part you want to retrieve (in this case, weekday), and the second is the date from which you want to extract the information (in this case, the current date returned by GETDATE()).
  • GETDATE(): This function returns the current database system timestamp.

Step 2: Running the Query

When you execute the above SELECT statement, it will return the name of the current day, for example “Sunday,” “Monday,” etc., making it much easier to understand at a glance.

Conclusion

By using the DATENAME function in SQL Server, you can effortlessly convert a numeric day representation to a more meaningful day name. This simple one-liner may save you time and make your data output significantly more interpretable, especially in reports or user interfaces.

Next time you need to retrieve the day of the week in SQL Server, remember that transforming a number into its name is just a quick query away!

Feel free to experiment with different dates by replacing GETDATE() with your specific date value to see how these functions work!