Date Storage, Date Comparison

Question

Post reply

Date Storage, Date Comparison

longobardia

SSC Eights!

Points: 907
More actions
January 8, 2009 at 7:25 am

#125418

I am trying to avoid wrapping code around my date type columns on some of the queries in stored procedures. Reason being that it causes the optimizer not to use an index on that date column.
The column type is DateTime which stores the complete date. ie: "2009-01-08 09:14:00 AM"
When I perform a query against this date field like so:
select * from table
where [dte_field] = '2008-01-09' <<< this doesnt match the date in the where clause.
How can I get a hit on the row without transforming the [dte_field] by wrapping with something like:
where convert( VARCHAR, [dte], 101) = '01/09/2009' <<< this will get a hit.
The point here is that I don't want to wrap functions around the datefield or convert the field.
I have used the DATEADD(dd, DATEDIFF(dd,0,[dte_field], 0) which eliminates the time side of the field and can equate to the arguable ('2009-01-09').
Any suggestions?
Thanks,
AL

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

DECLARE @Startdate DATETIME, @Enddate DATETIME

SELECT @Startdate = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0), -- 2009-01-08 00:00:00.000

@Enddate = DATEADD(dd, 1, @Startdate) -- 2009-01-09 00:00:00.000

SELECT ...

WHERE [dte_field] >= @Startdate AND [dte_field] < @Enddate

Cheers

ChrisM

^{“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

longobardia SSC Eights! Points: 907 More actions · Answer 2

Chris,

Thanks for your response.

I figured I would have to play around with the arguable side to leave the field alone.

Thanks again,

AL

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

Your assumptions were spot on, AL.

Thanks for the feedback.

^{“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