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
 Old Forums
 CLOSED - General SQL Server
 Comparing TIME

Author  Topic 

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-01-22 : 04:10:24
Is there an easy way to compare TIME SQL2K? We can use CONVERT function but that will be bit complicated.

Any suggestion?

------------------------
I think, therefore I am - Rene Descartes

Nazim
A custom title

1408 Posts

Posted - 2006-01-22 : 05:41:35
Have a look at DateDiff Function in BOL.

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-22 : 09:07:50
>> Is there an easy way to compare TIME SQL2K?
You want to compare time only or Date & time ?

>> We can use CONVERT function but that will be bit complicated.
What is the complication ? If your column is a datetime and you need to compare TIME only (without DATE), your query might suffer from performance issue.

Here illustrate 2 method to extract the TIME from the datetime.
declare @time1	datetime,
@time2 datetime

select @time1 = '2006-01-10 12:34:56',
@time2 = '2006-01-12 01:23:45'

-- This convert both @time1 & @time2 into same date (1900-01-01) such that you can compare the time.
-- Note : Resuls is a datetime
select tm1 = dateadd(day, datediff(day, 0, @time1) * -1, @time1),

-- This uses convert function + substring to extract the time.
-- Note : Result will be a varchar
tm2 = dateadd(day, datediff(day, 0, @time2) * -1, @time2)
select tm1 = substring(convert(varchar(25), @time1, 121), 12, 12), tm2 = substring(convert(varchar(25), @time2, 121), 12, 12)


-----------------
'KH'

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-23 : 01:12:23
Also refer
http://vyaskn.tripod.com/searching_date_time_values.htm

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-01-23 : 03:54:39
Thank you khtan and nadhivanan!
I found that khtan's solution is easier to implement.

------------------------
I think, therefore I am - Rene Descartes
Go to Top of Page
   

- Advertisement -