September 8, 2010 at 1:38 pm
Hi,
How would I compare time? What I would like to do is if the time is between 7am and 2pm I want to display 1, 2pm and 11pm display 2 and 11pm to 7am display 3. How would I do this?
For example, 2010-08-19 16:25:00.000 = 2
2010-08-20 22:45:00.000 = 2
2010-08-17 07:08:00.000 = 1
2010-08-29 03:12:00.000 = 3
2010-08-23 23:47:00.000 = 3
2010-08-15 10:42:00.000 = 1
Thanks,
September 8, 2010 at 2:01 pm
Code snippet: Alter as needed
CASE WHEN DATEPART( hour, @myDate) between 7 and 13 /*(1:59PM and lower)*/ then 1 when DATEPART( hour, @myDate) between 14 and 23 then 2 else 3 end
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 8, 2010 at 2:13 pm
I would prefer a solution that would be able to benefit from an index on that date column, or I would add shift table that would specify the period it's valid for as well as beginning and end of each shift (assuming we're talking about shift duration here - at least that's what it looks like). Therewith I wouldn't have to change the code if the shift time will change over time (in that case the query below would need to take the related date into consideration as well which easily might turn into a messy code...):
DECLARE @tbl TABLE
(
yourcol DATETIME
)
INSERT INTO @tbl
SELECT '2010-08-19 16:25:00.000' UNION ALL
SELECT '2010-08-20 22:45:00.000' UNION ALL
SELECT '2010-08-17 07:08:00.000' UNION ALL
SELECT '2010-08-29 03:12:00.000' UNION ALL
SELECT '2010-08-23 23:47:00.000' UNION ALL
SELECT '2010-08-15 10:42:00.000'
SELECT *,
CASE
WHEN yourcol>=DATEADD(dd,DATEDIFF(dd,0,yourcol),'07:00')
AND yourcol<DATEADD(dd,DATEDIFF(dd,0,yourcol),'14:00') THEN 1
WHEN yourcol>=DATEADD(dd,DATEDIFF(dd,0,yourcol),'14:00')
AND yourcol<DATEADD(dd,DATEDIFF(dd,0,yourcol),'23:00') THEN 2
ELSE 3 END AS shift
FROM @tbl
September 8, 2010 at 2:18 pm
Thank you!... that was what I was looking for.
September 8, 2010 at 2:19 pm
LutzM (9/8/2010)
I would prefer a solution that would be able to benefit from an index on that date column, (snip)
Lutz, since when does a case statement in a returned column care about the index levels? Sure, it needs to be included at the leaf level so you don't have to bookmark lookup back to the clustered, but otherwise your case here won't matter to an indexing.
If you were going to case statement in the where clause, though, you'd need to have a separate index on just the hours in a separate column (or indexed view doing the equivalent), as the date indexing would be completely scattered through the B-tree for the intended target, causing a scan anyway.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 8, 2010 at 2:56 pm
Craig Farrell (9/8/2010)
LutzM (9/8/2010)
I would prefer a solution that would be able to benefit from an index on that date column, (snip)Lutz, since when does a case statement in a returned column care about the index levels? Sure, it needs to be included at the leaf level so you don't have to bookmark lookup back to the clustered, but otherwise your case here won't matter to an indexing.
If you were going to case statement in the where clause, though, you'd need to have a separate index on just the hours in a separate column (or indexed view doing the equivalent), as the date indexing would be completely scattered through the B-tree for the intended target, causing a scan anyway.
Craig, you're absolutely correct regarding both statements! My fault. I'm sorry. :blush:
I usually try to avoid using DATEPART() at all due to the side effects of DATEPART(dw,) and DATEPART(wk,). Instead of trying to remember which datepart function should not be used (due to dependency of the DATEFIRST and/or LANGUAGE setting) I got used to avoid it whenever possible.
Again, sorry for the false argument and thank you for the correction.
September 8, 2010 at 3:04 pm
LutzM (9/8/2010)
Craig, you're absolutely correct regarding both statements! My fault. I'm sorry. :blush:I usually try to avoid using DATEPART() at all due to the side effects of DATEPART(dw,) and DATEPART(wk,). Instead of trying to remember which datepart function should not be used (due to dependency of the DATEFIRST and/or LANGUAGE setting) I got used to avoid it whenever possible.
Again, sorry for the false argument and thank you for the correction.
Heheh. 🙂 No problem, you had me scared for a moment that I had completely misunderstood a core component of the indexes. You had me rather worried. XD
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply