Conditional where statement

[emblewembl]

Hi,

I've got a stored procedure which returns a list of race data ordered by date and time where the race date is >= to today, i.e. all races that have not yet happened. The conditoinal bit of the stored procedure looks like this:

where tid.raceid = r.raceid
and m.meetingDate >= @today

It returns what I expected BUT what I actually want is time accurate data, so i only want the races where the race time is > than the time now. So I sort of want this:

where tid.raceid = r.raceid
and m.meetingDate >= @today
and racetime >= @theTime

but it doesn't quite work because it only returns races where the racetime is >= the time now regardless of the race date.

What I actually want is for it to return all races where the meetingdate >= todays date

AND if todays date = meetingdate THEN where racetime >= @theTime

.....but I don't know how to write this!!! Can anyone help??


i love chocolate

 

[druer]

The AND clause definitely evaluates and ensures that both conditions are met. What you have input appears to be sound code. Can you send the table definitions and some sample data?

 

[gmmastros]

Do you have 2 columns in your table, one for date and another for time? What are the data types of the MeetingDate and RaceTime fields?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[emblewembl]

No - i think you might be missing the point - basically I want to know how to include a conditional where statement. I want all data returned where the date is >= @today but if the date = @today i then want it to run an extra bit of the where statement so that it returns only the races whose racetime is > @theTime. Is that a bit clearer??!!

By the way my 2 variables of date and time are dd/mm/yyyy and hh:mm.

i love chocolate

 

[druer]

Oh.....

where
date >= @today
OR (date = @today AND time > @now)

Is what you are looking for.

 

[bborissov]

where tid.raceid = r.raceid
      and m.meetingDate >= @today
      AND CASE WHEN m.meetingDate = @today THEN 
                    racetime >= @theTime
               ELSE
                    1=1 END

?

Borislav Borissov

 

[emblewembl]

Yes thank you - !!!!!!!! Exactly what I wanted.

i love chocolate