DateTime comparison with some exceptions

I have StratDateTime and EndDateTime fields in the table. I need to compare this two datetime fields and find seconds. I can use DateDiff but there are the following exceptions:

1. Exclude seconds coming from the date which are Saturday and Sunday

2. Exclude seconds coming from time range between 7:01pm and 6:59am

3. Exclude seconds coming from Jan 1^st and Jul 4^th.

So do you want to make the difference between the two columns in seconds a column in the query results or do you want to compare them to each other or some other values in the WHERE clause of the query? I'd suggest that you post the query you have written and then one of us can help you with the query, as it is you have not really posted enough information for us to help you.|||

Ok. Thank you very much for your response.

SELECT Datediff(ss,StartDateTime,EndDateTime) AS mySeconds

FROM MyTable

This will return seconds. However this does not hold all the exceptions I listed above. Let’s say I have StartDateTime=12/15/2006 7:00pm and EndDateTime=12/18/2006 9:00am, then the difference should be 2 hours because between 12/15/2006 7:00pm and 12/18/2006 7:00am is not a business period, the rest is 2 business hours.

|||This is the same question asked by you before

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1022431&SiteID=1

I will again suggest you to use the calender-table. This will make life easy as you are not able to know 12/16/2006, is Saturday or working day.
With calender-table you can easily find that, more over you can create the holiday list too, find the difference between any date & more functionality can be add according to your own requirements.

Gurpreet S. Gill|||

Thank you very much for your help. That does not work for me since it is considering the day, not the time. My business day should be between 7:00am and 7:00pm in the weekdays. I do not see how getting number of business days would really help.

I would ask the same question, let’s say I have a calendar table, how would I get calendar table return me 2 hours for the following example. I have StartDateTime=12/15/2006 7:00pm and EndDateTime=12/18/2006 9:00am, then the difference should be 2 hours because between 12/15/2006 7:00pm and 12/18/2006 7:00am is not a business period, the rest is 2 business hours.

Thanks you very much for your help.

|||

What you need to do is create a user defined function to calculate your desired value. It will look like this, I haven't put in all the conditional code for you, that will take a while, but you get the idea.

CREATE FUNCTION BusinessSeconds(@.StartTime datetime, @.EndTime datetime)
RETURNS int
AS
BEGIN
DECLARE @.retVal int
SET @.retVal = datediff(ss, @.StartTime, @.EndTime)
--Conditional code here to subtract your non-business periods
--eg. IF ... SET @.retVal = @.retVal - 86400
RETURN @.retVal
END

And you'll use it like this

SELECT dbo.BusinessSeconds(StartDateTime, EndDateTime) AS MySeconds
FROM MyTable