datetime issues - 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

SQL Server 2005 Forums

Transact-SQL (2005)

datetime issues

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-05-06 : 10:44:32

Hi,

I have the following:

CASE WHEN (CONVERT(varchar, getdate(), 108) > '17:00:00') THEN CONVERT(varchar, getdate(), 108) - '17:00:00' ELSE '0' END AS end_time

Obviously this statement will not work; because the varchar is invalid for subtract operator.

Is there a way I can just keep this as a simple case statement, instead of writing a stored proc?

Thanks

Fahad

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-06 : 10:59:05

You can do this. Formatting should be done in the front-end, so I'll leave that to you
Jim

declare @date datetime

set @date = '10/10/2008 17:00:00'

select

case
when datepart(hour,@date) > 17
then dateadd(hour,-17,@date)
else 0
end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-07 : 02:47:09

declare @date datetime

set @date = '10/10/2008 17:00:00'

select
case
when datepart(hour,@date) > 17
then dateadd(hour,-17,@date)
else dateadd(hour,0,@date)
end

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-07 : 02:49:11

or

declare @date datetime

set @date = '10/10/2008 17:00:00'

select
case
when datepart(hour,@date) > 17
then dateadd(hour,-17,@date)
else @date
end

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources