Yesterday we had very first SQL Bangalore User Group meeting and I was asked following question right after the session.
“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.
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
)
Nice solution!
hi
we can use the following code also in SQL
SELECT CATEGORY =
CASE (DATENAME(DW,GETDATE()))
WHEN ‘SATURDAY’ THEN ‘WEEKEND’
WHEN ‘SUNDAY’ THEN ‘WEEKEND’
ELSE ‘WEEKDAY’
END
This has a flaw in it in that it doesn’t consider the setting of @@DATEFIRST. In the US, they number Sunday as 1, Monday as 2 etc. Most other countries use Monday as 1, Tuesday as 2.
This can be changed with the SET DATEFIRST command.
Here’s my attempt, it will give consistent results whatever the setting of @@DATEFIRST is:
SELECT today = datemaker.date,
name = DATENAME(dw, datemaker.date),
daynumber = 1 + ((DATEPART(dw, datemaker.date) + @@DATEFIRST – 2) % 7)
FROM (
– Make a list of dates starting from tomorrow
SELECT TOP 21
date = DATEADD(d, ROW_NUMBER() OVER (ORDER BY id), CONVERT(date, GETDATE()))
FROM sys.sysobjects
) datemaker
“I assume most of us are using SQL Server 2012…”
Evidently you don’t guys work in the real world. Do you also assume that most of us are using Windows 8?
Hi,
I need quick help.
I want to write a function which will take today’s date and reply me the number of weekdays only. For e.g. today is 04-28-2013. Result should be 20. How ?? today’s date minus 8 days as weekend passed. If today’s date is 0-17-2013 then result should be 12.
Catch is to know how many weekends are passed from today’s date. I need to calculate dynamic everyday.