Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to get the number of days between two different dates, except the weekends.

I don't know how to achieve this result.

share|improve this question
1  
Please post your table structure for reference.Weekends means SAT and SUN ? –  Abdul Manaf Jan 2 at 11:26
add comment

4 Answers

Assuming that by "weekend" you mean Saturday and Sunday, this can be even simpler:

SELECT count(*) AS count_days_no_weekend
FROM   generate_series('2014-01-01'::date, '2014-01-10'::date, '1 day') d(the_day)
WHERE  extract('ISODOW' FROM the_day) < 6;
  • You don't need an extra subquery level for generate_series(). SRF (set returning functions), also referred to as "table-functions", can be used just like tables in the FROM clause.

  • Note in particular that generate_series() includes the upper border in the output, as long as a full interval (3rd parameter) fits. The upper border is only excluded if the last interval would be truncated, which is not the case with full days.

  • With the pattern ISODOW for EXTRACT(), Sundays are reported as 7, according to the ISO standard. Allows for a simpler WHERE condition.

  • count(*) is slightly shorter and faster than count(the_day), doing the same in this case.

share|improve this answer
add comment

This example enumerates all dates between 2013-12-15 and 2014-01-02 (inclusively). The second column gives the day of week (numerically, between 0 and 6). The third column marks whether the day of week is a Saturday/Sunday or not (you'll have to adapt what you consider a weekend is) and is what could be used for counting weekdays.

select '2013-12-15'::date + i * interval '1 day',
       extract('dow' from '2013-12-15'::date + i * interval '1 day') as dow,
       case when extract('dow' from '2013-12-15'::date + i * interval '1 day') in (0, 6)
               then false
            else true end as is_weekday
from generate_series(0, '2014-01-02'::date - '2013-12-15'::date) i
;
share|improve this answer
add comment

Assuming that a weekend is Saturday and Sunday, you can use the following SQL.

select count(the_day) from 
    (select generate_series('2014-01-01'::date, '2014-01-10'::date, '1 day') as the_day) days
where extract('dow' from the_day) not in (0,6)

Replace the dates with your choices and the (0,6) with the days of the week you want to exclude.

A few things you need to take note of :-

  1. You have not mentioned what version of PostgreSQL you are running. This works on 9.1+ but should work on lower versions.

  2. The chosen dates are inclusive when using generate_series. So if you want days in between then add 1 day to each date.

share|improve this answer
    
I like your compact use of generate_series - I was not aware of the ability to use an interval for the third argumnet! –  yieldsfalsehood Jan 2 at 15:23
    
Your information about the last date being excluded is incorrect. Run a quick test! I added a corrective to my answer. –  Erwin Brandstetter Jan 2 at 16:21
1  
@Erwin Brandstetter You are correct. I have amended the note. Thank you. –  Imraan Jan 3 at 11:13
add comment

There are a couple of things you can do to make this easier. The method I would use would be to make sure that a table of dates is available. You can quickly create one like so:

CREATE TABLE [dbo].[Dates]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY(0,1),
[Date] Date NOT NULL unique,
isWeekend BIT NOT NULL DEFAULT(0)
)

Once the table is created, you should be able to populate it with date data relatively quickly.

set datefirst 6 --start date is saturday
INSERT INTO dbo.Dates(Date, isWeekend)
select 
    Date,
    case datepart(weekday,date) 
        --relies on DateFirst being set to 6
        when 2 then 1 
        when 1 then 1
        else 0
    end as isWeekend
from (
    select 
        dateadd(day, number - 1, 0) as date
    from (
        SELECT top 100000 row_number() over (order by o.object_id) as number
        FROM sys.objects o
            cross join sys.objects b
            cross join sys.objects c
    )numbers
)data

You can then write your query as a quick count of records from this table.

select count(*) as NumberOfWeekDays
from dbo.dates 
where isWeekend = 0
and date between '1 Jan 2013' and '31 Dec 2013'
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.