Need to compare time in HH:MM:SS format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need to compare time in HH:MM:SS format

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2011-12-08 : 19:31:04

I'm having a bear of a time comparing time in HH:MM:SS AM/PM format. It's got to be possible right? I wish the database was storing GMT time, not sure why it was setup this way.

My main goal is to return any values where the time field is less than 2 hrs old. This can come from either the sql or the web server.

Anyway, here's a couple examples of what I've been trying:


select * from table 
where date = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
      and [time] >= '16:24:22' 

or

select * from table
where date = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
      and [time] > '04:23:18 PM'

example of data from time field:


4:03:10 AM
4:04:16 AM
4:05:11 AM
5:19:52 PM
5:21:46 PM
5:22:05 PM
5:22:07 PM
9:05:08 PM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-08 : 20:20:21

as your time is in "HH:MM:SS AM/PM" format, when you are comparing "01:00:00 PM" with "02:00:00 AM", the "01:00:00 PM" will come before "02:00:00 AM".

You will need to convert the time to a proper date / time format before comparison.

date + ' ' + time will convert that to a datetime format and used it to compare with current time


where  date + ' ' + time > dateadd(hour, -2, getdate())

KH
[spoiler]Time is always against us[/spoiler]

crugerenator
Posting Yak Master

126 Posts

Posted - 2011-12-09 : 11:47:03

I actually just tried this and I'm getting the results I need. Your solution is a little cleaner though, thank you.


where [time] > convert(datetime, '5:23:18 PM', 8)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources