CODEWITHSUNDEEP
sql-servertime
Kerzoz
Kerzoz

Reputation: 331

How to compare Time value in table with Current Time in SQL?

I have a table named TimeList:

|    Slot    |
==============
|    10:00   |
|    11:00   |
|    12:00   |
|    13:00   | and so on

That saves the Times in Varchar(5)

The desired result should be showing the rows with time that is more than the current time, for example if the current time is 11:12 A.M. the result should return:

|    Slot    |
==============
|    12:00   |
|    13:00   |

I tried to Convert the two values into time and comparing them with:

SELECT * 
FROM TimeList
WHERE Convert(Time, Slot) > Convert(Time, GETDATE())

But it didn't work saying that Time is not a recognizable format in SQL

Is there anyway I could compare the two time slots?

Upvotes: 0

Views: 352

Answers (3)

Kerzoz
Kerzoz

Reputation: 331

Thank you very much for all the answers, fortunately I found the answer to my question inspired by your answers.

The solution is:

SELECT *
FROM TimeList
WHERE Slot > CONVERT(varchar(5),GETDATE(), 108)

Where it seems that 108 is the format for time saved as char/varchar in which Slot was categorized as too

Upvotes: 0

Elby Joy
Elby Joy

Reputation: 231

Try This.....

SELECT *
FROM    TimeList
WHERE Slot > CONVERT(time,GETDATE())

Upvotes: 2

Katerine459
Katerine459

Reputation: 485

Depends on the version of SQL Server you're running, I think. There is a CAST(.. as time) in 2012 or later, but I think that's a fairly new development. So... to compare the current date/time with the Timelist where the times are converted to "time, if it were today," something like this should work :

SELECT * 
FROM TimeList
WHERE Convert(Datetime, FORMAT (GETDATE(), 'd') + ' ' + Slot) > GETDATE()

Conversely, if you want to compare the times to the current time, as text:

SELECT * 
FROM TimeList
WHERE Slot > FORMAT(GETDATE(), N'hh\:mm')

Upvotes: 2

Related Questions