Search on a smalldatetime column

Question

Post reply

Search on a smalldatetime column

dubem1-878067

Ten Centuries

Points: 1044
More actions
January 3, 2009 at 2:39 pm

#129719

I have an apointment table which contains an apointment date column (smalldatetime). Value of this column is the date plus the time of the apointment.
My goal in to get all the apointments of a specific day.
My stored procedure for this request has one parameter (@specific_day smalldatetime) which contains a date (without time)
Select * from Apointment
Where apointment_date = @specific_day
This query always return 0 row and I know why, it's because all the apointments have a time and my parameter don't (well I guess it's 00:00:00) and so there is no matching.
So what should I do?
Than you
Martin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Select * from Apointment

Where apointment_date >= @specific_day AND apointment_date < DATEADD(DD, @specific_day, 1)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Just to be absolutely correct... the DATEADD portion of the code should be the following...

DATEADD(DD, 1, @specific_day)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Jeff Moden (1/3/2009)
Just to be absolutely correct... the DATEADD portion of the code should be the following...
DATEADD(DD, 1, @specific_day)

Oops...thanks Jeff.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden