Post reply

Date different exclued weekend,holiday and non-working hours

  • May 10, 2011 at 1:01 am

    #235990

    HI all,

    I have two date columns. i need date difference in HH:MM format that exclude weekend, holiday and non-working hours.

    Note holiday table is available with holiday date.

    Working hours is 5 AM to 7 PM GMT

    I guess many guys did this , if you have sample code then provide it.

    Thanks in advance

  • May 10, 2011 at 1:13 am

    #1322125

    Would the beloe help you at an extend?

    Declare @StartDate Date, @EndDate DateSet @StartDate = Getdate()-20Set @EndDate = Getdate() 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)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply