CODEWITHSUNDEEP
sqlsql-serverdatetimesql-server-2005
Prabhu
Prabhu

Reputation: 13325

Compare two time strings in SQL

I have a string variable that denotes a time:

@time = '5:00 PM'

I need to check if the current time getdate() is after or before @time. How can I do this in SQL?

Upvotes: 0

Views: 1544

Answers (3)

Mansi Mistry
Mansi Mistry

Reputation: 189

--@timeToCompare here,Replace your string

Declare @timeToCompare time =CONVERT(varchar(20),CONVERT(time,'17:30:00'), 114);
Declare @Currenttime time= CONVERT(varchar(20),CONVERT(time,GETDATE()), 114);

--Compare

if @timeToCompare > @Currenttime
 print 0
else
 print -1

You can also use case when condition.

Upvotes: 0

Metaphor
Metaphor

Reputation: 6395

This should do it:

SQL 2008+

if datediff(ss,cast(@time as time),cast(GetDate() as time)) < 0
   print 'Future'
else
   print 'Past'

Earlier:

if DatePart(hh,GETDATE())*60+DATEPART(mm,getDate()) < DatePart(hh,@time)*60+DATEPART(mm,@time)   
   print 'Future' 
else   
   Print 'Past'

Upvotes: 2

Sparky
Sparky

Reputation: 15085

One way, but not great on performance.

declare @time varchar(20)
set @time = '5:00pm'
select DatePart(hh,GETDATE())*60+DATEPART(mm,getDate()) as CurTime,
       DatePart(hh,@time)*60+DATEPART(mm,@time) as TheTime

Upvotes: 3

Related Questions