diff between varchar - 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)

diff between varchar

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-03-26 : 08:24:23

i have 2 columns of type vearchar(this is the time)

a b
1. 10:23 11:23
2. 05:47 05:48
3 04:59 05:04

i want all rows the diff more than 1 hours (i dont care the minutes)

i want just row 1 ,(if it cant,so we can diff just the hours i mean
,row 1 11-10>=1
,row 3 05-04>=1)

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-26 : 08:32:22

Try

select a,b from table
where replace(b,':','')*1-replace(1,':','')*1>=1000

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-03-26 : 08:42:01

I did it my way


declare @a varchar(10), @b varchar(10)
select @a = '10:23'
select @b = '11:23'

select
convert(int,substring(@b,1,2))*60 + convert(int,substring(@b,4,2)) as b,
convert(int,substring(@a,1,2))*60 + convert(int,substring(@a,4,2)) as a,
case
  when (convert(int,substring(@b,1,2))*60 + convert(int,substring(@b,4,2)))
       -
       (convert(int,substring(@a,1,2))*60 + convert(int,substring(@a,4,2)))
  >= 60.0
  then 'bingo!'
  else 'nono'
end as Result

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-26 : 09:17:14

My another way

declare @a varchar(10), @b varchar(10)
select @a = '10:23'
select @b = '11:23'

select	b/100*60+b%100 as b,
		a/100*60+a%100 as a,
	case 
		when b/100*60+b%100-a/100*60+a%100>=60.0 then 'bingo!'
		else 'nono'
	end as Result
from
(
	select replace(@a,':','')*1 as a,replace(@b,':','')*1 as b
) as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources