RSS 2.0 Feed
RSS 2.0


Atom 1.0 Feed
Atom 1.0

  Determining if a Date is a Weekday in T-SQL 


I was reminded of a SQL function to determine if a date was a weekday or a weekend I wrote a while back when I saw the requirements of a project a colleague was working on. You'll see this requirement fairly often in many business applications. A company might want to span certain activities over business or working days only. The requirement might be to only include business days in certain calculations. Either way, there are a few things to keep in mind when making this calculation.

Making the calculation can be very simple. It is easy code to write. However, in order to make sure the calculation works with the server's regional settings in mind you need to understand how the date settings effect the SQL date functions. A great example of the role that regional settings play in the results of the SQL date functions is outlined in this Informit.com article by Baya Pavliashvili:

The first day of the week can vary from country to country. In the US the week begins on Sunday, which is day 7 according to SQL server). Some calendars begin the week on Monday (which is day 1 according to SQL). However, getting the weekday datepart will return Sunday as 1 and Saturday as 7. The problem comes into play with international regional settings, where Sunday is not the start of the week, because the @@DATEFIRST setting in SQL is changed according to the calendar used, which then will effect the result from getting the weekday datepart (since it returns the value based on the @@DATEFIRST value). The solution to this is to take the @@DATEFIRST value into account in your calculation. As you can see from the article mentioned above that took a short trivial piece of code and turned it into a long drawn-out function.

I don't like long code when it could be shortened. Sure it might be easier to read, but I like to keep things short & compact. I take the @@DATEFIRST value minus 1 and add that to the weekday datepart minus 1. Then if that result modulus 7 equals either 5 or 6 then you have a weekend (Saturday or Sunday).

create function fn_IsWeekDay 
(
    @date datetime 
)
returns bit 
as 
begin 

    declare @dtfirst int
    declare @dtweek int 
    declare @iswkday bit 

    set @dtfirst = @@datefirst - 1
    set @dtweek = datepart(weekday, @date) - 1

    if (@dtfirst + @dtweek) % 7 not in (5, 6)
        set @iswkday = 1 --business day
    else
        set @iswkday = 0 --weekend

    return @iswkday
end

Short and sweet. Now you can simply do this:

if dbo.fn_IsWeekDay(@date) = 1 
begin 
    --do some magic here ;-)
end 

--or 

select a.SomeFieldsForCalculation from table a 
where dbo.fn_IsWeekDay(a.SomeDateField) = 1 

and all is good.




                   



Leave a comment below.

Comments

  1. Patrick 9/28/2005 5:18 AM
    Gravatar
    And what if the weekend is not saturday and sunday. Many payroll systems have customizable weekends. For example: Weekend starts at 11PM Friday and ends 7AM monday this putting your weekend falling between day numbers 5 and 1

    I am still looking for a function that takes into account a customizable weekend range.
  2. Ryan Farley 9/28/2005 7:18 AM
    Gravatar
    Patrick,

    This function could be customized just as you describe. You would just need to modify this line:

    if (@dtfirst + @dtweek) % 7 not in (5, 6)

    The "not in (5, 6)" part is what you will modify for different days of the week, those values could even be variables passed into the function. No big deal.

    -Ryan
  3. Mike Reaves 12/9/2005 11:11 AM
    Gravatar
    Nice work. Do you know of any other TSQL functions that replicate the date selection functions found in Crystal 10 such as LastFullMonth, LastFullWeek ?
  4. bill 2/7/2006 11:25 AM
    Gravatar
    What would be the best way to use this in creating a SQL server report?
  5. Ryan Farley 2/7/2006 11:46 AM
    Gravatar
    bill,

    It really depnds on what you needed to accomplish. There are a number of ways this could be used in a report, but that just depends on what you needed to be done. You could put it in a stored proc, view whatever.

    -Ryan
  6. bill 2/7/2006 12:33 PM
    Gravatar
    Ryan,
    I am creating a SP to retrieve the weekday count between to dates passed in as parameters. Now I just need to be able to pass the two parameter values on my SRS report in to the SP and assign the return value for use in other formulas on my report.

    - bill
  7. Chris 3/27/2006 3:59 AM
    Gravatar
    slightly longer and clearer is better than short and cryptic.
  8. ramesh 1/5/2007 6:02 AM
    Gravatar
    how to include time also in this calculation
    for example if the start date we are passing in is 2/1/07 7PM then it should start calculating from next day
    how to do this
    any ideas
  9. Danny 2/5/2007 5:13 AM
    Gravatar
    I have some difficult question. How can i count days but i don't need the first weekend. I need the Datediff of plandate and measuredate.
    If the plandate is thursdate than is friday the first day. And Monday is the second day etc.
    If the plandate is friday then is firstday the first day and is monday the second day.
    How can solve this . function or Sp .

    Any ideas
  10. Gravatar
  11. Gravatar
  12. MikerMiker 8/2/2007 7:16 AM
    Gravatar
    I didn't realize what a squirrely issue this was until I started playing with it.
    Many ways to solve a problem tho and here is another:

    --CHECKING FOR DAY OF WEEK
    /***********************
    -- Have to FORCE in time of 12:00:00.000 as anytime before that (actually 11:59:59.994)
    -- results in date conversion of 1 day before.
    -- Current TIME of when query is run affects return value.
    -- THEREFORE workaround is to force in the TIME
    select CAST( cast('2007-08-01 11:59:59.994' as DATETIME) as INT) --results in return of 39293
    select CAST( cast('2007-08-01 11:59:59.995' as DATETIME) as INT) --results in return of 39294
    ************************/
    DECLARE @varDayOfWeek INT
    SET @varDayOfWeek =((CAST(cast((RTRIM(CONVERT(CHAR(20),getdate(),101))+' 12:00:00.000') as DATETIME) as INT)%7))
    select @varDayOfWeek

    SELECT CASE
    WHEN @varDayOfWeek=0 THEN 'SUNDAY'
    WHEN @varDayOfWeek=1 THEN 'MONDAY'
    WHEN @varDayOfWeek=2 THEN 'TUESDAY'
    WHEN @varDayOfWeek=3 THEN 'WEDNESDAY'
    WHEN @varDayOfWeek=4 THEN 'THURSDAY'
    WHEN @varDayOfWeek=5 THEN 'FRIDAY'
    WHEN @varDayOfWeek=6 THEN 'SATURDAY'
    END

    IF (@varDayOfWeek=0 OR @varDayOfWeek=6)
    SELECT 'WEEKEND'
    ELSE
    SELECT 'WORK DAY'

  13. MikerMiker 8/2/2007 10:39 AM
    Gravatar
    Reason I came up wit the above method was due to not wanting to contend with setting of "SET DATEFIRST" which Select DATEPART(Dw,getdate()) does.
    FYI.
  14. Peter 5/20/2008 12:44 AM
    Gravatar
    Hi,

    I'm trying to find out how to break a specificied Date range into weeks.

    I have the following Date Range:
    Start Date : 01/01/2008
    End Date: 02/30/2008

    I want it to display as followed, Eg:

    For Jan:

    Week 1 (01/01/2008 - 05/01/2008)
    Week 2 (06/01/2008 - 12/01/2008)
    Week 3 (13/01/2008 - 19/01/2008)
    Week 4 (20/01/2008 - 26/01/2008)
    Week 5 (27/01/2008 - 31/01/2008)

    For Feb:

    Week 1 (01/02/2008 - 02/02/2008)
    Week 2 (03/02/2008 - 09/02/2008)
    Week 3 (10/02/2008 - 16/02/2008)
    Week 4 (17/02/2008 - 23/02/2008)
    Week 5 (24/02/2008 - 30/02/2008)

    It must break the month into weeks and display as above.

    If anyone has any other ideas, please let me know.

    Thanks
  15. mrWoodo 10/30/2008 9:12 PM
    Gravatar
    Add this where clause to only return rows on weekdays

    select getdate()
    where datename(dw, getdate()) not in ('Saturday', 'Sunday')


  16. markow 12/2/2008 3:42 AM
    Gravatar
    You can just use
    DATEPART(dw, GETDATE())
    to get yourself the day of a week!
  17. Ashley 2/22/2009 11:29 PM
    Gravatar
    The Informit article demonstrates the sum of datepart + datefirst is 7 or 14 for Sat, and always 8 for Sunday. So I'm curious why you subtracted 1 from both and then did % 7? A minutely quicker way might be:
    ------------
    if (@@datefirst + datepart(weekday, @date)) % 7 not in (0, 1)
    ------------

    And maybe slightly quicker still:
    ------------
    if (@@datefirst + datepart(weekday, @date)) not in (7, 14, 8)
    ------------
    I.e. no % calculation.
  18. Elias Chatzigeorgiou 5/5/2009 10:03 AM
    Gravatar
    To exclude Sat,Sun you should write:

    if (@dtfirst + @dtweek) % 7 not in (0, 6)

    instead of

    if (@dtfirst + @dtweek) % 7 not in (5, 6)
  19. Darth Continent 1/28/2010 8:39 AM
    Gravatar
    Thanks for this, seems like a solid solution, I have a SQL job that needs to conform to some business logic for a particular entity such that certain work is done only on weekdays and this does the trick.
  20. Sunil 3/9/2010 2:25 PM
    Gravatar
    Thanks. This was a great help. The URL to this article is now enshrined in a comment in a stored procedure in our database -- LOL.
  21. Ryan Farley 3/9/2010 2:32 PM
    Gravatar
    @Sunil LOL! Glad it helped.
  22. Rick Arthur 5/18/2010 5:43 AM
    Gravatar
    Nice!
  23. Mike 7/27/2010 1:01 AM
    Gravatar
    The solution is not elegant enough to handle the international case. What if @@DATEFIRST is not 7 to begin with? What if language is not English (check for DATENAME 'SUNDAY')? I guess one has to write a stored procedure instead of function to have a solution for ALL cases.
  24. Ryan Farley 7/27/2010 8:43 AM
    Gravatar
    Mike,

    This is true and something to be aware of, if the server language is not set to English then it could change the outcome of @@DATEFIRST (See http://msdn.microsoft.com/en-us/library/ms187766.aspx)

    -Ryan
  25. Mike 7/28/2010 12:25 AM
    Gravatar
    I use this code now. Not elegant but I think it will work.

    SELECT @Datesubtract =
    CASE DATEPART(dw,@Date)
    WHEN (8-@@DATEFIRST) THEN -2 --Sunday
    WHEN (8-@@DATEFIRST)%7+1 THEN -3 --Monday
    ELSE -1
    END

    SELECT @PreviousWeekday = DATEADD(dw,@Datesubtract,@Date)

    Would have been a lot easier if Microsoft could have distinguished between a weekday and day though (dw is supposed to be weekday, but is in this case handled like a day) ...
  26. 4/26/2011 6:39 AM
    Gravatar
    Determine if a day is a weekday using TSQL « The weird world of BI
  27. 3/9/2014 3:56 AM
    Gravatar
    Finding Day in T-SQL | Search RounD
Comments have been closed on this topic.



 

News


Also see my CRM Developer blog

Connect:              


Sponsor

Sections