GetDate() returns the Date+Time, not just the Time. DateTimes look like floating point decimals, where the whole number portion is the number days past 1/1/1900 (or 1901 depending on your software), and the decimal portion is the time within the day even spread out over the 24 hour period. Your database is storing just the time, which is REALLY the Date+Time, or Date=0 + Time. Compare that with getdate(), which for today would be 38278.3849234 or something like that, instead of 0.3849234.
Adjust your query to be something like this: SELECT (stuff) from SHOWS WHERE Starttime <= getdate() - convert(datetime,convert(int,getdate())) AND endtime >= getdate() - convert(datetime,convert(int,getdate())) Probably not the best way, as there must be cleaner methods, but that might work. I'm taking Date+Time and subtracting Date to be left with the fractional Time part. If I did that right, it resets the getdate() to day 0, so you get just the time. -----Original Message----- From: Michael Kear [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 20, 2004 10:05 To: SQL Subject: Time functions I have a radio station application, and we need to display "who's on air now" on the site. We're doing that from a SQLServer database where each show has a start time and a finish time in a datetime field. It's displaying in the form 09:00:00AM when I look at it in Enterprise Manager. How do I find the show that's currently playing? This select statement gives me zero records: SELECT (stuff) from SHOWS WHERE Starttime <= getdate() AND endtime >= getdate() But I'd have thought that would do it? No? Cheers Mike Kear Windsor, NSW, Australia AFP Webworks http://afpwebworks.com .com,.net,.org domains from AUD$20/Year ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=38 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2060 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
