SQL time difference query problem

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 2008 Forums

Transact-SQL (2008)

SQL time difference query problem

Author

Topic

farrukh635
Starting Member

5 Posts

Posted - 2012-02-11 : 03:19:02

Hi,

I am having a problem with the following query

select DATEDIFF(HH,convert(datetime,'19:52'),convert(datetime,'05:00'))

the above query giving me result -14 but it should be 9,
Please help me to figure out the problem.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-11 : 06:41:34

you does not have a date in there, it datediff() will not know the '05:00' is which date. In this case, what it does is to find the difference in hour. If the 1st date/time is later than 2nd date/time, it returns a negative value

You can use a CASE statement to check for which date/time is later and pass in the date/time to correct parameter. Also you don't need to convert to datetime


declare @t1 varchar(5), @t2 varchar(5)
-- @t1 is FROM time, @t2 is TO time
select  case when @t1 < @t2
        then datediff(hour, @t1, @t2)
        else datediff(hour, @t1, dateadd(day, 1, @t2))
        end

KH
[spoiler]Time is always against us[/spoiler]

farrukh635
Starting Member

5 Posts

Posted - 2012-02-11 : 07:45:48

Hi KH,
Thank you for the reply. But my problem is little different
the query 1. gives my 9 but query 2. gives my -15,it should be -9 but not -15

The following query calculate time difference from 8AM to 5 PM
1. select DATEDIFF(HH,convert(datetime,'08:00'),convert(datetime,'17:00'))

The following query calculate the time difference from 8PM to 5AM
2. select DATEDIFF(HH,convert(datetime,'20:00'),convert(datetime,'05:00'))

Thank you for your response.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-11 : 07:52:18

edited my last post

KH
[spoiler]Time is always against us[/spoiler]

farrukh635
Starting Member

5 Posts

Posted - 2012-02-11 : 08:18:49

getting following error,

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

declare @t1 varchar(5)='19:52', @t2 varchar(2)='05:00'
-- @t1 is FROM time, @t2 is TO time
select case when @t1 < @t2
then datediff(hour, @t1, @t2)
else datediff(hour, @t1, dateadd(day, 1, @t2))
end

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-11 : 08:31:40

sorry typo error. Edited my last post

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-11 : 08:32:49

quote:
Originally posted by farrukh635

getting following error,

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

declare @t1 varchar(5)='19:52', @t2 varchar(2)='05:00'
-- @t1 is FROM time, @t2 is TO time
select case when @t1 < @t2
then datediff(hour, @t1, @t2)
else datediff(hour, @t1, dateadd(day, 1, @t2))
end

why are you using varchar fields?
use datetime instead as its date values you're dealing with

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-11 : 08:33:16

quote:
Originally posted by farrukh635

getting following error,

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

declare @t1 varchar(5)='19:52', @t2 varchar(5)='05:00'
-- @t1 is FROM time, @t2 is TO time
select case when @t1 < @t2
then datediff(hour, @t1, @t2)
else datediff(hour, @t1, dateadd(day, 1, @t2))
end

Your varchar needs to be longer - see in red above.

farrukh635
Starting Member

5 Posts

Posted - 2012-02-11 : 08:57:49

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources