Customized Date/time Difference

[sharath82]

A query is been posted.The time is captured as From_time.When some one answers for the query that time is captured as To_Time.I need the difference between these to as


Official timings are 9.00 am to 6.00 pm
1.only official time is taken into consideration.A query posted after office hours is taken as 6.00 pm.
2.saturdays and sundays are not considered.
3.i have a table as holiday master,so those days are not considered.


Ex:a query is being posted at 4 pm today and it is solved at 2pm tomorrow.
so my answer should be 7 hours.


a query is being posted on friday 3 pm and and is solved on next tuesday 11 am.so friday 3 hours.monday 9 hours and tuesday 2 hours.so totally diff is 14 hours.


can we do with sql query alone or need to use asp as well.
I need to implement this.Please help me out.


Regards,

Sharath.

 

[The_Doc_Man]

The EASIEST way I see to handle this is to define an "EFFECTIVE" posting date that is related to the "ACTUAL" posting date but through the filters of your stated rules. Then measure your times from the effective rather than the actual date.

 

[TessB]

I'm actually trying to do the exact same thing. However, I have no control over the fields in the database. (Meaning, I can't just add a field "effective date" to the table)
I have the LOGIC mapped out on how to manipulate the orders received outside of normal business days / hours.

My problem, and I am hoping it has a simple solution, is how to separate the TIME from the DATE to check to see if the Time received is between 6 pm and 9 am.

The field I have to pull this information from is a regular "DATE" field 'mm/dd/yyyy hh24:mi:ss'

Any insight?

 

[pdx_man]

can we do with sql query alone or need to use asp as well.

The language (TSQL) isn't the problem, creating the algorithm is our challenge. I will give you the framework and you can fill in a couple of the areas I noted to create a function:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @StartTime FLOAT
DECLARE @EndTime FLOAT
DECLARE @MinusDay TINYINT

SELECT @StartDate = '11/16/2006 16:00'

SELECT @StartTime = convert(float,@StartDate - dbo.WholeDate(@StartDate))
SELECT @EndDate = '11/17/2006 14:00'

SELECT @EndTime = convert(float,@EndDate - dbo.WholeDate(@EndDate))
SELECT @EndDate = dbo.WholeDate(@EndDate)

IF @EndTime > .75   -- 6:00 pm
	SELECT @EndTime = .75
ELSE IF @EndTime < .375  -- If the time is < 9:00 am, set to 6:00 pm, the previous day
BEGIN
	SELECT @EndTime = .75
	SELECT @MinusDay = (1)  -- Replace wit function to check if the current day is Monday or day after holiday, might have to subtract more than one day
	SELECT @EndDate = DATEADD(DAY,-@MinusDay,@EndDate) 
END

SELECT ROUND((.75 - @StartTime) * 24 +		-- Time on posting day
	(@EndTime - .375) * 24 +		-- Time on solution day
	(DATEDIFF(DAY,@StartDate,@EndDate) - 1) * 9,2) 	-- Replace with function elimanating Holidays and weekends

I use a function I created called WholeDate in which I drop off the fractional portion of the day. Handy thing to have:

ALTER   FUNCTION dbo.WholeDate(@MyDate SMALLDATETIME)
RETURNS SMALLDATETIME AS
BEGIN
	RETURN CONVERT(SMALLDATETIME, CONVERT(CHAR(10), @MyDate, 101))
END