SQL SERVER – Find Weekend and Weekdays from Datetime in SQL Server 2012

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

SQL SERVER - Find Weekend and Weekdays from Datetime in SQL Server 2012 getdatedisplay2

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

SQL SERVER - Find Weekend and Weekdays from Datetime in SQL Server 2012 getdatedisplay1

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 (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQLAuthority News – Android Efficiency Tips and Tricks – Personal Technology Tip
Next Post
SQL SERVER – Sends backups to a Network Folder, FTP Server, Dropbox, Google Drive or Amazon S3

Related Posts

17 Comments. Leave new

  • Nice solution!

    Reply
  • 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

    Reply
  • Alan Singfield
    December 6, 2012 3:03 pm

    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

    Reply
  • “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?

    Reply
  • 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.

    Reply
    • Danielle Paquette-Harvey
      August 20, 2013 9:17 pm

      Did you find a solution? I need to find the number of weekdays between two dates. Thanks

      Reply
      • Danielle Paquette-Harvey
        August 20, 2013 9:22 pm

        I just found this! Very usefull!

        DECLARE @StartDate DATETIME
        DECLARE @EndDate DATETIME
        SET @StartDate = ‘2008/10/01’
        SET @EndDate = ‘2008/10/31’

        SELECT
        (DATEDIFF(dd, @StartDate, @EndDate) + 1)
        -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
        -(CASE WHEN DATENAME(dw, @StartDate) = ‘Sunday’ THEN 1 ELSE 0 END)
        -(CASE WHEN DATENAME(dw, @EndDate) = ‘Saturday’ THEN 1 ELSE 0 END)

      • Shreenath Ayachit
        November 6, 2015 11:19 pm

        Really, this query helps a lot. I got the way I was looking for. A ton thanks for posting this code.

  • ScottPletcher
    July 5, 2013 8:12 pm

    To me, that code is *far* too dependent on specific date and language settings, aside from having extra overhead. How about instead:
    SELECT DATEDIFF(DAY, 0, A.ModifiedDate) % 7 <= 4 THEN 'Weekday' ELSE 'Weekend' END
    That code has *NO* reliance on date or language settings to determine the correct result.

    Reply
  • hi, can anyone tell me how to filter datetime column based on current date, weekly and monthly.
    Select * from timeattendance where clmdatetime = GETDATE() <== that is for datetime now, but I want the date without the time.
    How about filter weekly (monday to sunday) and monthly (ist of month and end of month)?

    Reply
  • Hi,
    My Table have week_end_date(Friday) and days of week as column, but you know some time weeks overlap in two months that causing wrong month wise report. Any suggestion how to consolidate data coreectly month wise.

    Reply
  • CREATE FUNCTION FNS_GETDATEBYWEEKDAY( @YEAR AS INT=2015,@MNTH AS INT=8, @DAY AS INT =6, @WEEK AS INT =5)
    RETURNS DATETIME
    AS
    BEGIN
    /*
    0 -> Monday
    1 -> Tuesday
    2 -> Wednesday
    3 -> Thursday
    4 -> Friday
    5 -> Saturday
    6 -> Sunday
    */
    DECLARE @DATE AS DATETIME
    SET @DATE=CONVERT(DATETIME, CONVERT(VARCHAR,@YEAR)+RIGHT(’00’+CONVERT(VARCHAR,@MNTH),2) + RIGHT(’00’+CONVERT(VARCHAR,1 * @WEEK),2) )
    RETURN(SELECT DATEADD(DAY,DATEDIFF(DAY,0,@DATE-(@DAY+@WEEK))/7*7+(7*@WEEK),@DAY))
    END

    Reply
  • Hi All,

    i give you one small task please write the query please any one solve this query.

    with out using date functions how to find out the days or months and years from one date to another date
    with out using date functions in SQL server.

    Thanks
    kiran.

    Reply
  • Hi,

    i want to get the week number of the year by passing the date in the where clause. Can you please help.

    Regards,
    Shivani Sharma

    Reply
  • Jothi Chitrambalam
    July 29, 2016 12:04 pm

    Nice Solution….
    But U can use the below also,
    SELECT GETDATE() Today,DATENAME(dw, GETDATE()) DayofWeek,Case when DATENAME(dw, GETDATE()) IN (‘Saturday’,’Sunday’) then ‘WeekEnd’ else ‘WeekDay’ end WorkDay

    Reply
  • That’s a great way to do the job. Thanks for posting ist!!

    Reply

Leave a Reply

Menu