Showing records after a specific time

Databases
1

Hi guys

I hope this is one that you can help me with.

Bascially I have the following table:

Eventtitle | Eventdate | Starttime
New Balance Trainer Release | 2016-03-26 | 15:00:00
Nike Trainer Release | 2016-08-26 | 15:00:00
Puma Trainer Release | 2016-10-11 | 09:56:00

The “eventdate” datatype is “Date” in MS SQL Server
The “starttime” datatype is “time(0)” in MS SQL Server

I’m currently querystring my table so that I can display the single event that is either on today or closest to today’s date (a date in the future). To do this is use:

select top 1 eventid, eventdate, starttime
from mytable
where eventdate >= CONVERT(date,GETDATE())
order by eventdate

This works perfectly and using the above exmaple my page would display “Nike Trainer release” as the “New balance” event is in the past. However, I have a problem if there are two events on the same day like so:

Eventtitle | Eventdate | Starttime
New Balance Trainer Release | 2016-03-26 | 15:00:00
Nike Trainer Release | 2016-08-26 | 15:00:00
Asics Trainer Release | 2016-08-26 | 12:00:00
Puma Trainer Release | 2016-10-11 | 09:56:00

My script will rightly display the “Asics Trainer Release” record as it’s earlier in the day however…

and here’s my problem…

what if I only want to display events that are at the current time or at a time in the future? So, if today is the 26th Aug 2016 and the current time happens to be 2pm then how would I get my statement display the “Nike Trainer Release” record?

It’s like I needs something like

where eventdate >= CONVERT(date,GETDATE()) AND where starttime >= CONVERT(time,GETTIME())

but this is just made up and won’t work. I have been on this for 4 day and still can’t find the solution.

If anyone can help then I would be fully appreciated

Best regards

Rod from the UK

2

only one WHERE keyword is permitted

because you have separate date and time columns (sorry for the lecture again), your query needs a bit more complexity

[code]WHERE eventdate > GETDATE()
OR (
eventdate = CONVERT(DATE,GETDATE())
AND starttime >= CONVERT(TIME,GETDATE())
)

[/code]

3

Hi r937

Thank you so much for your reply, much appreciated.

The additional WHERE was a typo, sorry about this.

I tried your suggestion but I could not get it to work. It’s like the “starttime >= CONVERT(TIME,GETDATE())” doesn’t actually work as it should.

I tried the “starttime >= CONVERT(TIME,GETDATE())” in isolation in a separate script and it did not find any records that were after the current time. I know I have hundreds of records with start times after the time I ran the script therefore I think the issue lies here.

The starttime values in my table are in the following format

hh:mm:ss | (time(0)

May this be the problem?

Again, any help would be fully appreciated. I look forward to hearing from you

Best regards

Rod from the UK

4

please run these two and see if anything jumps out at you–

SELECT TOP 10 starttime , CONVERT(TIME,GETDATE()) AS now FROM mytable ORDER BY starttime ASC

and

SELECT TOP 10 starttime , CONVERT(TIME,GETDATE()) AS now FROM mytable ORDER BY starttime DESC

5

Hi r937

Thanks again for getting back to me.

I’m now really confused as when I run these two scripts I simply get 10 of my records either ordered by start time ascending or starttime descending.

Am I missing something? I forgot to say I am an absolute novice at SQL statements.

Again, any help would be fully appreciated

Best regards

Rod from the UK

6

Hi r937

I see where you’re coming from now!

I “think” I have managed to make it do what I need with your fantastic help!

Thanks again!

Best regards

Rod from the UK

7

please show us what the solution is

8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.