CODEWITHSUNDEEP
sqlsql-server-2008datetime-format
azalikaEriya
azalikaEriya

Reputation: 195

How to compare Hour and Minute only from DateTime data type

I have field endTime has DateTime data type and status (int). I want to update my status field from 1 to 0 if hour and minute from endTime is equal to hour and minute of current time. the day is absolutely same. so the day is doesn't matter.

How do I do ?

Upvotes: 1

Views: 7103

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use datepart():

select (case when datepart(hour, EndTime) = datepart(hour, getdate()) and
                  datepart(minute, EndTime) = datepart(minute, getdate())
             then 1 else 0
        end) as status

You can also put this into an update, if that is what you really want.

EDIT: The update would be:

update table
    set status = (case when datepart(hour, EndTime) = datepart(hour, getdate()) and
                            datepart(minute, EndTime) = datepart(minute, getdate())
                       then 1 else 0
                  end);

Upvotes: 1

azalikaEriya
azalikaEriya

Reputation: 195

UPDATE <table> SET status = 0
WHERE datepart(hour, endTime) = datepart(hour,getdate())
AND datepart(minute, endTime) = datepart(minute,getdate());

This is How I do on my SQL-Server 2008.

Upvotes: 1

abalos
abalos

Reputation: 1361

Try this:

UPDATE <TABLE> 
SET STATUS = 1
WHERE MINUTE(GETDATE()) = MINUTE(ENDTIME)
   AND HOUR(GETDATE()) = HOUR(ENDTIME)

Upvotes: 0

Related Questions