Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JVisconti
Starting Member
47 Posts |
 Posted - 2009-10-06 : 08:12:53
|
| I have the following piece from a CASE statement that I thought was working correctly:WHEN (DATEPART(dw, TicketCloseDateTime) >= 2) AND (DATEPART(dw, TicketCloseDateTime) <= 5) AND (DATEPART(hh, TicketCloseDateTime) > 17)THEN CONVERT(varchar(10), DATEADD(dd,DATEDIFF(dd,0,TicketCloseDateTime),0), 101) + ' 17:00:00'ELSE TicketCloseDateTimeCurrently it checks if the day of the week is between 2 and 5, and is after 5pm, if so then it should set the result to the current day and 17:00. I have one record that was opened on September 4th and closed at 14:45 on September 5th. This statement is returning my TicketCloseDateTime as the 4th at 17:00. Shouldn't it just return the TicketCloseDateTime as it shows in that field? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-06 : 08:15:35
|
14:45 is not "> 17"
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-06 : 08:17:58
|
Ups - yes that is what you mean....
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
JVisconti
Starting Member
47 Posts |
Posted - 2009-10-06 : 08:22:14
|
| So, since 14:45 is not > 17, shouldnt it just return the TicketCloseDateTime without removing a day? The record opened on 9-4, closed at 14:45 on 9-5, but my result is returning the TicketCloseDateTime as 9-4 at 17:00. I'm still confused. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-06 : 08:22:45
|
Ok, I have tested your statement and it works like expected.Maybe there is something in your data...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
JVisconti
Starting Member
47 Posts |
Posted - 2009-10-06 : 08:28:16
|
| There very well could be. I'll run some more checks and post back what I find.Thanks! |
|
|
|
JVisconti
Starting Member
47 Posts |
Posted - 2009-10-06 : 08:53:31
|
| Let me post the full CASE statement in case I'm missing something else:CASE WHEN DATEPART(dw, TicketCloseDateTime) = 1 THEN CONVERT(varchar(10), DATEADD(dd,DATEDIFF(dd,0,TicketCloseDateTime),-2), 101) + ' 17:00:00' WHEN DATEPART(dw, TicketCloseDateTime) = 7 THEN CONVERT(varchar(10), DATEADD(dd,DATEDIFF(dd,0,TicketCloseDateTime),-1), 101) + ' 17:00:00' WHEN (DATEPART(dw, TicketCloseDateTime) >= 3) AND (DATEPART(dw, TicketCloseDateTime) <= 6) AND (DATEPART(hh, TicketCloseDateTime) < 8) THEN CONVERT(varchar(10), DATEADD(dd,DATEDIFF(dd,0,TicketCloseDateTime),-1), 101) + ' 17:00:00' WHEN (DATEPART(dw, TicketCloseDateTime) >= 2) AND (DATEPART(dw, TicketCloseDateTime) <= 5) AND (DATEPART(hh, TicketCloseDateTime) > 17) THEN CONVERT(varchar(10), DATEADD(dd,DATEDIFF(dd,0,TicketCloseDateTime),0), 101) + ' 17:00:00' WHEN (DATEPART(dw, TicketCloseDateTime) = 2) AND (DATEPART(hh, TicketCloseDateTime) < 8) THEN CONVERT(varchar(10), DATEADD(dd,DATEDIFF(dd,0,TicketCloseDateTime),-3), 101) + ' 17:00:00' ELSE TicketCloseDateTime ENDThe record that made me aware of this problem doesnt seem to meet any of the cases, and so should return the TicketCloseDateTime, but it's not. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-06 : 09:10:51
|
What datatype is TicketCloseDateTime in the table?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
mhorseman
Starting Member
44 Posts |
Posted - 2009-10-06 : 09:14:48
|
| Assuming your Datefirst setting is the same as mine, then September 5th (Saturday) gives Day of Week of 7, so is caught by the second When of your Case statement. If you use October 5th (Monday) you get Day of Week of 2, and the TicketCloseDate is unchanged.Mark |
|
|
|
JVisconti
Starting Member
47 Posts |
Posted - 2009-10-06 : 09:18:31
|
| it is set as a datetime field. I think I found out why it returned 9-4 @17:00. In the case where dw = 7 it is clamping the day back one day and setting it to 17:00. I'm thinking maybe I should change it to:CONVERT(varchar(10), DATEADD(dd,DATEDIFF(dd,0,TicketCloseDateTime),2), 101) + ' 08:00:00'Is my thinking correct? Instead of clamping back one day if it closes on dw = 7, then add 2 days and clamp it to 08:00? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-06 : 09:39:37
|
Maybe that's correct but it is not easy for me to follow because in your first post there was nothing about dw=7.Is September 5th giving you a 7 like mhorseman said?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
JVisconti
Starting Member
47 Posts |
Posted - 2009-10-06 : 09:42:24
|
| Yes it is. I think my first post should have had the full case statement in it. My apologies, I didn't see the dw problem until just before I posted the full statement. |
|
|
|
|
|
|
|
|
Case statement