About the author

Vijay Kodali
E-mail me Send mail

Site Statistics

Site Meter

Recent comments



The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2017

Comparing Time part in DateTime SQL

if a column in DB table is a datetime and you need to compare TIME only (without DATE) , there is no built in function for that.

Here is small Query to perform that task. The key is converting both to same Date

@time1 datetime  
@time2 datetime 
select @time1 = '1900-01-01 07:30:00.000',
 @time2 = '1899-12-30 07:30:00.000' 
-- 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) 
---Comapre tm1 & tm2 


Thanks to tip from "khtan" of SqlTeam

Categories: SQL server
Posted by vijay on Wednesday, May 14, 2008 11:46 AM
Permalink | Comments (2) | Post RSSRSS comment feed


Peso Sweden

Sunday, November 16, 2008 2:11 PM


set tm1 = convert(varchar(25), @time1, 114)
set tm2 = convert(varchar(25), @time2, 114)

Kevin United States

Friday, February 10, 2012 1:16 PM


Can compare times by using CAST(@time1 AS time) = CAST(@time2 AS time)

Add comment

(Will show your Gravatar icon)

  Country flag

  • Comment
  • Preview