time clamping problem - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

time clamping problem

Author

Topic

JVisconti
Starting Member

47 Posts

Posted - 2009-10-08 : 13:21:50

I have a case statement part witch should set a datetime field depending on a day of week and specific time condition:

UPDATE TicketMetrics SET TicketOpenBusDateTime =
WHEN (DATEPART(dw, TicketOpenDateTime) >= 2) AND (DATEPART(dw, TicketOpenDateTime) <= 5) AND (DATEPART(hh, TicketOpenDateTime) > 17)
THEN CONVERT(varchar(10), DATEADD(dd,DATEDIFF(dd,0,TicketOpenDateTime),0), 101) + ' 08:00:00'

The sample datetime is TicketOpenDateTime = 9-1-2009 and the time is 17:49. If my statment above is correct it should clamp the time of 9-1-2009 17:00. But it currently is returning the original TicketOpenDateTime of 9-1-2009 17:49. What am I doing wrong?

JVisconti
Starting Member

47 Posts

Posted - 2009-10-08 : 13:31:48

edit of my first post, the end of the statement ' 08:00:00' should read as ' 17:00:00'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-08 : 13:39:30

UPDATE TicketMetrics SET TicketOpenBusDateTime =
WHEN (DATEPART(dw, TicketOpenDateTime) >= 2) AND (DATEPART(dw, TicketOpenDateTime) <= 5) AND (DATEPART(hh, TicketOpenDateTime) >= 17)
THEN CONVERT(varchar(10), DATEADD(dd,DATEDIFF(dd,0,TicketOpenDateTime),0), 101) + ' 08:00:00'

Jim

I wrote this to check it. Your code will need a CASE and END statement, I think.

DECLARE @TicketOpenDateTime datetime
SET @TicketOpenDateTime = '9-1-2009 17:49'

select @TicketOpenDateTime

select DATEPART(dw, @TicketOpenDateTime) ,DATEPART(hh, @TicketOpenDateTime),
case
WHEN (DATEPART(dw, @TicketOpenDateTime) >= 2)
AND (DATEPART(dw, @TicketOpenDateTime) <= 5)
AND (DATEPART(hh, @TicketOpenDateTime) >= 17)
THEN DATEADD(hour,8,DATEADD(dd,DATEDIFF(dd,0,@TicketOpenDateTime),0) )
ELSE @TicketOpenDateTime
end

Everyday I learn something that somebody else already knew

JVisconti
Starting Member

47 Posts

Posted - 2009-10-08 : 13:44:56

That took care of the problem. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources