Click here to Skip to main content
12,448,720 members (53,187 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

50K views
1 bookmarked
Posted

Simple query to get weekdays between two date fields

, 12 Jul 2011 CPOL
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.

You may also be interested in...

Comments and Discussions

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

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web01 | 2.8.160811.3 | Last Updated 12 Jul 2011
Article Copyright 2011 by GregStevens
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid