CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Gallop
Gallop

Reputation: 1405

Time Compare SQL Query

I am getting the current system time with the following query 

select cast(DATEPART(hh,getdate()) as varchar)+ ':'+ cast(DATEPART(n,getdate()) as varchar)

Now how do I compare this current system time with a time field to see which is larger?

 -----------------------
 declare    @time1  datetime,
            @time2  datetime

 select  @time1 = '20060701 02:27:35.35',
         @time2 = getdate()

 select tm1, tm2,
        case    
         when tm1 = tm2 then 'tm1 = tm2' 
         when tm1 > tm2 then 'tm1 > tm2'
    else        'tm1 < tm2'
    end as [TimeDiff]
 from
 (
     select dateadd(day, -datediff(day, 0, @time1), @time1) as tm1,
            dateadd(day, -datediff(day, 0, @time2), @time2) as tm2
 ) t

 -----------------------

The time field has time in the following format

     0400
     0415
     0430

I see this supporting Function. Now how do I alter the @time1 part to "select time from table"

Thanks

Upvotes: 0

Views: 986

Answers (5)

John Fraser
John Fraser

Reputation: 58

To get the time in that format is

select REPLACE( CONVERT(VARCHAR(5),CURRENT_TIMESTAMP,108), ':', '')

Upvotes: 0

Rohini
Rohini

Reputation: 21

If you just want to replace @time1 with a table column. In that case, here you go,

declare    @time2  datetime

CREATE Table #temp
(
time1 varchar(100)

)
Insert into #temp 
 select '20060701 02:27:35.35'

Insert into #temp 
 select '20060701 12:27:35.35'

 Insert into #temp 
 select '20060701 22:27:35.35'

 Select   @time2 = getdate()

 select tm1, tm2,
        case    
         when tm1 = tm2 then 'tm1 = tm2' 
         when tm1 > tm2 then 'tm1 > tm2'
         else        'tm1 < tm2'
        end as [TimeDiff]
 from
 (
     select dateadd(day, -datediff(day, 0, time1), time1) as tm1,
            dateadd(day, -datediff(day, 0, @time2), @time2) as tm2

        FROM #temp
 ) t

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

select case when cast(cast(DATEPART(hh,getdate()) as varchar)+ ':'+ cast(DATEPART(n,getdate()) as varchar) as datetime) >
       cast(left('0415',2)+':'+right('0415',2) as datetime) then 'greter' else 'lesser'
       end

Upvotes: 1

ngruson
ngruson

Reputation: 1338

You can get the time like this:

select replace(cast(cast(getdate() as time) as varchar(5)), ':', '')

It gets the first 5 characters from the time and then removes the :.

Upvotes: 0

AnandPhadke
AnandPhadke

Reputation: 13486

Declare @time varchar(10)
SET @time='0400'
select CASE when convert(datetime,left(@time,2)+':'+RIGHT(@time,2))>convert(datetime,cast(DATEPART(hh,getdate()) as varchar)+ ':'+ cast(DATEPART(n,getdate()) as varchar)) then '1' else '0' end

Upvotes: 0

Related Questions