Results 1 to 14 of 14

Thread: Calculating time

  1. #1
    Join Date
    Aug 2005
    Posts
    31

    Calculating time

    Hello,
    I am fairly new to this, but I need to write a query that calculates whether an order made a shipping cutoff based on creation time, and when it actually shipped, based on local customer time. All times in the table below are central time.
    Heres where it gets complicated for me. If the order was created after 8:00am local customer time the same day, and shipped prior to 5pm local customer time, it would be considered a pass, or a 1. If an order was created after 8am local customer time, BUT didnt ship until AFTER 5pm local customer time, it would be considered a failure or a 0. And to complicate things even more, if the order is created after 5pm local customer time, the order create time would be treated as it was created at 8am local customer time the following day local customer time, and would need to be shipped prior to 5pm local customer time that day. Would CASE be appropriate for this? Or am I thinking wrong? Any help would really be appreciated!!


    Ordernum OrderCreate OrderShipDate TimeZ
    67890 5/5/2005 11:57 5/6/2005 15:33 EST
    35789 5/5/2005 13:57 5/5/2005 14:19 MST
    44465 5/5/2005 13:58 5/5/2005 21:58 CST
    87654 5/5/2005 18:00 5/7/2005 21:58 PST

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    You can try case with datepart function.

  3. #3
    Join Date
    Aug 2005
    Posts
    5
    right on, u can use a case to check for both the cases

    obain the datepart of dd and check for same date then convert order date into hh:mm:ss am\pm see convert in sql help this will give you 4 columns which are

    orderdatedatepart(dd) 5 according to 1st case in the above case

    orderdatetime assume 8:20am in morning

    shipdatedatepart(dd) 5

    shipdatetime 4:45 PM assume

    this will give me output 1 which is my flag

    now check if (ord datepart = shipdatepart)
    based on ord no USING A CURSOR
    if true
    then
    check ord date > 8 am and ship date < 5 pm then flag = 1
    else
    if ship datetime > 5 pm then shipdatepart = shipdatepart + 1
    else
    flag = 0
    Last edited by msarvind; 08-29-2005 at 01:37 PM.

  4. #4
    Join Date
    Aug 2005
    Posts
    31
    That looks something like the way I need to go, I am very new to this all, and I cant seem to get "case" to work properly, and I am not familiar with using cursors. This is what I have so far using datepart in the query. Somehow, I need to also factor in the time zone. All times given are central. Thanks for the responses and help!

    Query:
    SELECT order,log_date,shipdate
    datepart(dd,log_date) AS 'CREATEHOUR',
    datepart(dd,ship_date) AS 'SHIPHOUR'
    FROM [dbo].[tblshipDetail]

    I have uploaded the results in a txt file, as I wasnt able to make it very readable pasting it here.
    Attached Files Attached Files

  5. #5
    Join Date
    Dec 2004
    Posts
    502
    Maybe there's an easier way to do this, but I just did it the straightforward way:

    Code:
    SELECT ordernum, ordercreate, ordershipdate, timez,
    --If created after 8am and shipped before 5pm on the same day
    CASE	WHEN DATEDIFF	(d, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate), 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
    			) = 0 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
    			) >= 8 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
    			) < 17 THEN 1 
    --If created after 8am and shipped after 5pm on the same day
    	WHEN DATEDIFF	(d, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate), 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
    			) = 0 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
    			) >= 8 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
    			) >= 17 THEN 0 
    --If created after 5pm and shipped before 5pm on the next day
    	WHEN DATEDIFF	(d, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate), 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
    			) = 1 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
    			) >= 17 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
    			) < 17 THEN 1 
    ELSE 0 END
    FROM [dbo].[tblshipDetail]

  6. #6
    Join Date
    Aug 2005
    Posts
    31
    That works great!!! Awesome Job! Thanks Nosepicker!!

  7. #7
    Join Date
    Aug 2005
    Posts
    31
    I would like to go 1 step further with this, and calculate weekends differently, than normal business days. The rule that I have to change is if it is 5pm or later on a friday, it is counted as being set up on the next monday. Saturdays and Sunday ordercreate dates are treated as being set up on the following Monday as well.

    I have been able to convert the ordercreate dates to be shown as the following Monday, BUT, it is counting some as a miss-ship, even if it may have shipped on the saturday, or sunday the order was set up in ordershipdate.

    Even after converting, it works fine as long as the ordershipdate is before 5pm, regardless of if its a weekend day or not.

    I have tried to use a datepart to compare both dates, so the ordercreatedate would be greater than the ordershipdate, and be bucketed as a 1, or pass. But that is also changing the result set of the normal 5 day business work week data as well. Any help would really be appreciated! I am to the point where I am dreaming about this query.

  8. #8
    Join Date
    Dec 2004
    Posts
    502
    I need more info. If for example an order is created on Friday at 6PM and then shipped on Saturday at 9AM, is that a pass or failure? How about created on Friday 6PM and shipped on Monday at 7AM? Created on Friday 6PM and shipped on Monday at 4PM? Created on Friday 6PM and shipped on Monday at 6PM? Any other scenario I missed?

  9. #9
    Join Date
    Aug 2005
    Posts
    31
    ""If for example an order is created on Friday at 6PM and then shipped on Saturday at 9AM''

    That would be a pass, anything created at or after 5pm local customer time on Friday only needs to ship prior to 5pm local customer time Monday.

    "How about created on Friday 6PM and shipped on Monday at 7AM?"

    This would be a pass as well, if created at or after 5pm local customer time would only fail if it ships after 5pm Monday.

    "Created on Friday 6PM and shipped on Monday at 6PM?"

    This would be a fail, but just by a minute. If the create time was 5pm local customer time or later that night, it would have to ship by 4:59pm Monday local customer time to pass.
    But you have the right idea! Just prior to 1700

    So basically anything created at or after 5pm customer time on friday, needs to ship prior to 5pm Monday local customer time.

    Anything created Saturday or Sunday, needs to ship by 5pm Monday local customer time. The rules need to stay the same
    for normal business days as before.


    Thanks for your help!

  10. #10
    Join Date
    Dec 2004
    Posts
    502
    OK, give this a spin and see how it works (I think I got the signs wrong in my earlier SQL statement when trying to adjust for the time zones):

    Code:
    SELECT ordernum, ordercreate, ordershipdate, timez,
    --If created after Friday, 5pm and shipped before 5pm on the following Monday
    CASE	WHEN DATEPART	(dw, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate)
    			) = 6 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate)
    			) >= 17 
    	AND DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate) > 
    		DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate) 
    	AND DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate) < 
    		DATEADD(hh, 17, DATEADD(wk, DATEDIFF(wk, 0, DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate)) + 
    		CASE WHEN DATEPART(dw, DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate)) = 1 THEN 0 ELSE 1 END, 0))
    	THEN 1
    --If created after 8am and shipped before 5pm on the same day
    	WHEN DATEDIFF	(d, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate), 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate)
    			) = 0 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate)
    			) >= 8 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate)
    			) < 17 
    	THEN 1 
    --If created after 8am and shipped after 5pm on the same day
    	WHEN DATEDIFF	(d, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate), 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate)
    			) = 0 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate)
    			) >= 8 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate)
    			) >= 17 
    	THEN 0 
    --If created after 5pm and shipped before 5pm on the next day
    	WHEN DATEDIFF	(d, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate), 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate)
    			) = 1 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate)
    			) >= 17 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate)
    			) < 17 
    	THEN 1 
    ELSE 0 END
    FROM [dbo].[tblshipDetail]

  11. #11
    Join Date
    Aug 2005
    Posts
    31
    The time zones were functioning correctly previously. This even corrected an issue I was seeing when the month would change over, it was counting things for example, created on 1/31, and failing them if shipped on 2/1. But thats working correctly now

    Is there a way to get this to treat anything created on Saturday or Sunday at any time of day to just have a cutoff time of 5pm Monday? Pretty much the same thing as you did with Friday, but with no constraints on what time it was created on saturday or sunday. It just needs to ship by 5pm monday.

    Also, it is failing anything that is created after 5pm, and ships prior to Midnight the same night? For example, this is counting as a fail against the real data, but should not.

    Timez ordercreate ordershipdate
    CST 2/1/2005 17:28 2/1/2005 17:28 0

    Thanks alot for your help Nosepicker, you are awesome with this stuff. I am truly grateful.

  12. #12
    Join Date
    Dec 2004
    Posts
    502
    I reordered the CASE conditions and put the time zone adjustments back to the way I had them before (still doesn't seem right, but I'll trust your assessment):

    Code:
    SELECT ordernum, ordercreate, ordershipdate, timez,
    --If created after Friday, 5pm and shipped before 5pm on the following Monday
    CASE	WHEN 	
    	(
    		(
    	DATEPART	(dw, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
    			) = 6 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
    			) >= 17 
    		)
    	OR DATEPART	(dw, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
    			) IN (1, 7)
    	)
    	AND DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate) > 
    		DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate) 
    	AND DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate) < 
    		DATEADD(hh, 17, DATEADD(wk, DATEDIFF(wk, 0, DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)) + 
    		CASE WHEN DATEPART(dw, DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)) = 1 THEN 0 ELSE 1 END, 0))
    	THEN 1
    --If created after 5pm and shipped before 5pm on the next day
    	WHEN ordercreate <= ordershipdate
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
    			) >= 17 
    	AND ordershipdate < DATEADD(hh, 17, DATEADD(d, DATEDIFF(d, 0, ordercreate) +1, 0))
    	THEN 1 
    --If created after 8am and shipped before 5pm on the same day
    	WHEN DATEDIFF	(d, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate), 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
    			) = 0 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
    			) >= 8 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
    			) < 17 
    	THEN 1 
    --If created after 8am and shipped after 5pm on the same day
    	WHEN DATEDIFF	(d, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate), 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
    			) = 0 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
    			) >= 8 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
    			) >= 17 
    	THEN 0 
    ELSE 0 END
    FROM [dbo].[tblshipDetail]

  13. #13
    Join Date
    Dec 2004
    Posts
    502
    Oops, forgot to put in the time zone adjustments everywhere:

    Code:
    SELECT ordernum, ordercreate, ordershipdate, timez,
    --If created after Friday, 5pm and shipped before 5pm on the following Monday
    CASE	WHEN 	
    	(
    		(
    	DATEPART	(dw, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
    			) = 6 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
    			) >= 17 
    		)
    	OR DATEPART	(dw, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
    			) IN (1, 7)
    	)
    	AND DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate) > 
    		DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate) 
    	AND DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate) < 
    		DATEADD(hh, 17, DATEADD(wk, DATEDIFF(wk, 0, DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)) + 
    		CASE WHEN DATEPART(dw, DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)) = 1 THEN 0 ELSE 1 END, 0))
    	THEN 1
    --If created after 5pm and shipped before 5pm on the next day
    	WHEN DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate) <= 
    		DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
    			) >= 17 
    	AND DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate) < 
    		DATEADD(hh, 17, DATEADD(d, DATEDIFF(d, 0, DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)) +1, 0))
    	THEN 1 
    --If created after 8am and shipped before 5pm on the same day
    	WHEN DATEDIFF	(d, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate), 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
    			) = 0 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
    			) >= 8 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
    			) < 17 
    	THEN 1 
    --If created after 8am and shipped after 5pm on the same day
    	WHEN DATEDIFF	(d, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate), 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
    			) = 0 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
    			) >= 8 
    	AND DATEPART	(hh, 
    			DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
    			) >= 17 
    	THEN 0 
    ELSE 0 END
    FROM [dbo].[tblshipDetail]

  14. #14
    Join Date
    Aug 2005
    Posts
    31
    It works! I was scrubbing the data all evening, and I have found nothing wrong at all. I will look a bit more, but this looks right!!

    As far as the time zones, I am in central time, as is the time in the data. so thats the reason it needs to add an hour to EST, and drop 1 hour for MST, 2 for PST etc. thanks for your help!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •