locked
How to exclude Saturday and Sunday while calculating the difference of hours between two days? RRS feed

  • Question

  • Hi All,

    Can you please help me how to exclude the hours of saturdays and sundays?

     

    I am using currently : select datediff(hh,resolveddate,createdate) from xyz

    Thanks in Advance

    Prince Kher


    Prince Kher
    Monday, April 25, 2011 3:49 PM

Answers

  • Hi Prince Kher,

    Is your question Master Data Services related ?

    anyway, I've found an interesting post about your question :

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=158935

    here are the details below:

    Select
    IncidentNumber,
    CreatedDateTime,
    ResolutionDateAndTime,
    OwnerTeam,
    Priority,
    Subject,
    TypeofIncident,
    [hours] = DATEDIFF(hh,CreatedDateTime,ResolutionDateAndTime),
    [minutes] = DATEDIFF(mi,CreatedDateTime,ResolutionDateAndTime),
    [seconds] = DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime),
    [display] = convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)/3600)+' hours '+
    right('00'+convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)%3600/60),2)+' minutes '+
    right('00'+convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)%3600%60),2)+' seconds '

    FROM dbo.Incident
    --ORDER BY CreatedDateTime

    GO

    Regards,



    Xavier Averbouch
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".
    Tuesday, April 26, 2011 7:12 AM