Yesterday we had very first SQL Bangalore User Group meeting and I was asked following question right after the session. This question is about to Find Weekend and Weekdays from Datetime in SQL Server 2012.
“How do we know if today is a weekend or weekday using SQL Server Functions?”
Well, I assume most of us are using SQL Server 2012 so I will suggest following solution. I am using SQL Server 2012’s CHOOSE function. It is
SELECT GETDATE() Today, DATENAME(dw, GETDATE()) DayofWeek, CHOOSE(DATEPART(dw, GETDATE()), 'WEEKEND','Weekday', 'Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay GO
You can use the choose function on table as well. Here is the quick example of the same.
USE AdventureWorks2012 GO SELECT A.ModifiedDate, DATENAME(dw, A.ModifiedDate) DayofWeek, CHOOSE(DATEPART(dw, A.ModifiedDate), 'WEEKEND','Weekday', 'Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay FROM [Person].[Address] A GO
If you are using an earlier version of the SQL Server you can use a CASE statement instead of CHOOSE function. All system date and time values are derived from the operating system of the computer on which the instance of SQL Server is running.
Please read my earlier article which discusses CHOOSE function and CASE statements. Logical Function – CHOOSE() – A Quick Introduction
Reference: Pinal Dave (http://blog.SQLAuthority.com)