November 29, 2005 at 11:00 pm
Comparing only Time in Sql?
Hi Dears!
I have two Different DateTime Values i want to Compare only their times independent of Dates How to do it?
let
DateTime @dt1='2004-11-29 19:30:00.917'
DateTime @dt2='2005-11-29 15:00:00.917'
accordint to my requirements
@dt1>=@dt2 should be true as 19:30>=15:00
because i have to compare only the time not the Date how to achieve this in sql Function required.
Thnx In Advance
November 30, 2005 at 3:08 am
Hi,
you can do this by using the CONVERT function and the style option to specify that you only want the time:
select
@date1=convert(datetime,convert(varchar(8),@date1,8))
November 30, 2005 at 7:14 am
You can nest DATEPART comparisons. For instance, look at the hours, then the minutes, then the seconds.
K. Brian Kelley
@kbriankelley
December 1, 2005 at 1:54 am
DECLARE @dt1 datetime, @dt2 datetime
SET @dt1='2004-11-28 19:30:00.917'
SET @dt2='2005-11-29 15:00:00.917'
PRINT 'With date'
SELECT @dt1, @dt2, CASE WHEN @dt1 >= @dt2 THEN 'True' ELSE 'False' END, 'Expression should be False as is not 2004-11-28 >= 2004-11-29'
SET @dt1=CONVERT(varchar,@dt1,114)
SET @dt2=CONVERT(varchar,@dt2,114)
PRINT 'Without date'
SELECT @dt1, @dt2, CASE WHEN @dt1 >= @dt2 THEN 'True' ELSE 'False' END, 'Expression should be True as is 19:30 >= 15:00'
Andy
December 1, 2005 at 5:17 am
If you are doing this a lot, you should separate the data into two different columns - one storing dates with time values of 0, and one storing times with date values of 0. This will allow SQL Server to use an index on the columns, significantly speeding up your searches..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply