Search on a smalldatetime column

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Blogs QotD Books SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Search on a smalldatetime column

Search on a smalldatetime column

Rate Topic

Display Mode

Topic Options

Author

Message

dubem1

Posted Saturday, January 03, 2009 2:39 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 28, 2009 11:51 AM
Points: 21, Visits: 73

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

Post #629352

Chris Morris

Posted Saturday, January 03, 2009 2:56 PM

SSCommitted

Group: General Forum Members
Last Login: Monday, June 22, 2009 4:48 AM
Points: 1,759, Visits: 3,754

Select * from Apointment
Where apointment_date >= @specific_day AND apointment_date < DATEADD(DD, @specific_day, 1)

^{Low-hanging fruit picker}

For better assistance in answering your questions, please read this.

Post #629355

Jeff Moden

Posted Saturday, January 03, 2009 8:54 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:30 PM
Points: 16,212, Visits: 8,849

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."

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #629410

Chris Morris

Posted Sunday, January 04, 2009 3:27 AM

SSCommitted

Group: General Forum Members
Last Login: Monday, June 22, 2009 4:48 AM
Points: 1,759, Visits: 3,754

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.

^{Low-hanging fruit picker}

For better assistance in answering your questions, please read this.

Post #629433

« Prev Topic | Next Topic »

Permissions