Menu
I've been doing a convert(varchar,datefield,112) on each date field that I'm using in 'between' queries in SQL server to ensure that I'm only accounting for dates and not missing any based on the time part of datetime fields.
Now, I'm hearing that the converts aren't indexable and that there are better methods, in SQL Server 2005, to compare the date part of datetimes in a query to determine if dates fall in a range.
What is the optimal, indexable, method of doing something like this:
select * from appointments where appointmentDate>='08-01-2008' and appointmentDate<'08-15-2008' 
792
You may cast getdate() to date - this will cut off time. Or use date range if delivery_date stored may contain time too (if it is so I'd suggest to use this approach instead of casting both sides to date ).
The right way to compare date only values with a DateTime column is by using <= and > condition. This will ensure that you will get rows where date starts from midnight and ends before midnight e.g. dates starting with '00:00:00.000' and ends at "59:59:59.999".
Here we will see, SQL Query to compare two dates. This can be easily done using equals to(=), less than(<), and greater than(>) operators. In SQL, the date value has DATE datatype which accepts date in 'yyyy-mm-dd' format. To compare two dates, we will declare two dates and compare them using the IF-ELSE statement.
The best way to strip the time portion of a datetime field is using datediff and dateadd functions.
DateAdd(day, datediff(day,0, MydateValue), 0) This takes advantedge of the fact that SQL Server stores dates as two integers, one representing the number of days since day "0" - (1 jan 1900), and the second one which represents the number of ticks (each tick is about 3.33 ms) since midnight (for the time) *.
the formula above simply has to only read the first integer. There is no conversion or processing required, so it is extremely fast.
To make your queries use an index... use this formula on the input filtering parameters first, or on the "other" side of the equal sign from the tables date time field, so that the query optimizer does not have to run the calculation on every datetime field in the table to determine which rows satisfy the filter predicate. This makes your search argument "SARG-able" (Search ARGument)
Where MyDateTimeColumn > DateAdd(day, datediff(day,0, @MydateParameter), 0) -- SARG-able rather than
Where DateAdd(day, datediff(day,0, MyDateTimeColumn ), 0) > @MydateParameter -- Not SARG-able * NOTE. Internally, the second integer (the time part) stores ticks. In a day there are 24 x 60 X 60 X 300 = 25,920,000 ticks (serendipitously just below the max value a 32 bit integer can hold). However, you do not need to worry about this when arithmetically modifying a datetime... When adding or subtracting values from datetimes you can treat the value as a fraction as though it was exactly equal to the fractional portion of a day, as though the complete datetime value was a floating point number consisting of an integer portion representing the date and the fractional portion representing the time). i.e.,
`Declare @Dt DateTime Set @Dt = getdate() Set @Dt = @Dt + 1.0/24 -- Adds one hour Select @Dt Set @Dt = @Dt - .25 -- Moves back 6 hours Select @Dt` If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
