Click here to Skip to main content
Click here to Skip to main content

Tagged as

Simple query to get weekdays between two date fields

, 12 Jul 2011
Rate this:
Please Sign up or sign in to vote.
How to get weekdays between two date fields without a stored function

Sometimes you have two date fields in a table and you want to return the number of week days between them (i.e., the number of days excluding the weekend).

On the web, you will find several ways to program stored functions in code blocks (in PL/SQL or SQL Server or whatever your favorite flavor is) that will compute this function. But this isn't necessary.

You can use a simple expression in your SQL statement that will return the number of week days between two date fields.

The general strategy is to construct an expression that does the following:

  • Take the raw difference in days
  • Subtract 2 days for every full 7 days that has passed
  • If the second date falls on an earlier day of the week than the first date, add another two days for the most recent weekend that has passed
  • Correct the final total for anomalies when one or the other day actually falls on a weekend

Using Oracle SQL:

SELECT
    date1,
    date2,
    (date2-date1)-2*FLOOR((date2-date1)/7)-DECODE(SIGN(TO_CHAR(date2,'D')-
        TO_CHAR(date1,'D')),-1,2,0)+DECODE(TO_CHAR(date1,'D'),7,1,0)-
        DECODE(TO_CHAR(date2,'D'),7,1,0) as WorkDays
  FROM
    datetest
  ORDER BY date1,date2

(I know that date formatting and certain functions like DECODE are different for MySQL and SQL Server. Can someone provide the equivalent of this expression using MySQL or SQL Server syntax? Put it in a comment and I will add it here, or you can just post it as an alternate. In theory, the logic of this expression could be reproduced in any flavor of SQL.)

This function will return the number of days between date1 and date2, excluding weekends. Here is a result of this query using a table with some sample data:

DATE1          DATE2          WORKDAYS
7/1/2011       7/20/2011      13
7/2/2011       7/20/2011      13
7/3/2011       7/20/2011      13
7/4/2011       7/4/2011       0
7/4/2011       7/5/2011       1
7/4/2011       7/6/2011       2
7/4/2011       7/7/2011       3
7/4/2011       7/8/2011       4
7/4/2011       7/9/2011       4
7/4/2011       7/10/2011      4
7/4/2011       7/11/2011      5
7/4/2011       7/12/2011      6
7/4/2011       7/13/2011      7
7/4/2011       7/14/2011      8
7/4/2011       7/15/2011      9
7/4/2011       7/16/2011      9
7/4/2011       7/17/2011      9
7/4/2011       7/18/2011      10
7/4/2011       7/19/2011      11
7/4/2011       7/20/2011      12

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

GregStevens
Web Developer
United States United States
I'm just some guy.
Follow on   Twitter

Comments and Discussions

 
GeneralRe: Fantastic! I will update the tip with this version. Im' onl... PinmemberGregStevens14-Jul-11 9:20 
GeneralRe: In the end it will be this query. when startdate == enddate... Pinmemberdigimanus14-Jul-11 4:34 
GeneralRe: I think t will be: DECLARE @StartDate DATETIME DECLARE @EndD... Pinmemberdigimanus14-Jul-11 4:24 
GeneralRe: "If the weekday of the EndDate is before the weekdate of the... Pinmemberdigimanus14-Jul-11 4:16 
GeneralRe: Well, as I suspected: it returns 4, even though the correct ... PinmemberGregStevens14-Jul-11 4:08 
GeneralRe: I thought that 3 would be the fact that 19-15 = 5 days! if 1... Pinmemberdigimanus13-Jul-11 21:37 
GeneralRe: the answer is 4 Pinmemberdigimanus13-Jul-11 21:35 
GeneralRe: How fantastically weird. I wonder why? In the example where... PinmemberGregStevens13-Jul-11 8:55 
GeneralRe: wrong... it returns 3 Pinmemberdigimanus13-Jul-11 7:49 
GeneralRe: P.S. if you have access to an SQL server database, please ch... PinmemberGregStevens13-Jul-11 6:35 
GeneralRe: I don't think that works, because it only accommodates 3 of ... PinmemberGregStevens13-Jul-11 6:34 
GeneralRe: I found this on the internet: DECLARE @StartDate DATETIME DE... Pinmemberdigimanus13-Jul-11 4:17 
GeneralRe: SELECT DATEDIFF(d, date1, date2) as Days FROM TABLE This do... PinmemberGregStevens13-Jul-11 4:03 
Generalwhat about &lt;pre&gt;<code> SELECT DATEDIFF(d, date1, date2... Pinmemberdigimanus12-Jul-11 22:47 
Generalhi i am getting error like there is no built in function of ... PinmemberLakshmiNarayana Nalluri12-Jul-11 21:26 
GeneralRe: digimanus - 8 secs ago what about &lt;pre&gt; SELECT DATEDIF... Pinmemberdigimanus12-Jul-11 22:48 
GeneralRe: Digimanus is correct: if you notice in the tip above, it say... PinmemberGregStevens13-Jul-11 3:20 
GeneralWhile your tip will find weekdays, it doesn't account for ho... PinsubeditorWalt Fair, Jr.12-Jul-11 16:06 
GeneralRe: A good observation, and an important distinction, you are co... PinmemberGregStevens12-Jul-11 16:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 12 Jul 2011
Article Copyright 2011 by GregStevens
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid