January 7, 2003 at 1:59 pm
I know this is not a bug, but I have found this to be an annoyance. When I have a select statement that looks for dates >= ‘12/1/02’ and dates <= ‘12/31/02’, I do not return any rows for the last day of the month. The reason is because there are times with these dates. There are several ways to fix this but what is the most efficient way? I have been changing all of my code to add one day to the end date and changing the operator to less than (<). Once again, is there a more efficient way to writing the select statement to get records for a specific date without worrying about the time?
January 7, 2003 at 2:12 pm
You are right in the way you decided to resolve the issue. Adding a day to the @date_to is the best solution I think. Of course, it is not a bug. Just get used to it.
WHERE dates >= @date_from
AND dates < DATEADD( dd, 1, @date_to )
or like this:
WHERE dates BETWEEEN @date_from AND DATEADD( dd, 1, @date_to )
By the way, which one is faster?
January 7, 2003 at 2:33 pm
You could also do
WHERE convert(char,dates,101) BETWEEN '12/01/02' AND '12/31/02'
As for which is faster, it seems between is but when you look at the Execution Plan it reads back as "dates >= @date1 AND dates <= @date2" which looks screwy as it is the first one. I do believe there is a slight compiler advantage to the BETWEEN as opposed to the first thou in the actual parsing and index decisions in the query manager. It may have to do with the fact that BETWEEN will always be val1 AND val2 where the other could be >= AND some other condition AND another condition AND <= in the where clause and the query engine has to figure related portions of the query out to execute. It however would never be worse as BETWEEN and is much easier to read.
January 7, 2003 at 5:12 pm
I use the method antares686 suggests as a standard. I haven't found anything more efficient as of yet.....
January 7, 2003 at 5:49 pm
The method Antares686 suggested works of course but it won't use index on the dates column if such exists because there is a function applied to the column. I am still recommending to perform operations on the constant @date_to, bot on the date column.
In addition, you may want to execute
SET @date_to_plus_one = DATEADD( dd, 1, @date_to )
before the query and use it so that it does not run the function for every row.
January 7, 2003 at 6:04 pm
I agree with you Antares686. I try to ensure that my variables are set before executing the statement. However there are times that I don't have the luxury. Thanks for the information about the performance hit that is taken when it has to execute the function (DateAdd) everytime. I forget about these things sometimes.
January 8, 2003 at 4:21 am
Ok
couple additional pieces about this I think I should include since I brought it up.
WHERE convert(char,dates,101) BETWEEN '12/01/02' AND '12/31/02'
If does definently not use an index if one exists so it is slow.
ALso, I forgot if it were like this
WHERE convert(char,dates,101) BETWEEN '12/01/02' AND '12/31/03'
it will not pick up any values for 01-11 2003 do to it will perform a char compare (alpha order left to right) instead of numeric compare so although good for simple day or within the same month on small tables or non-indexed fields it will suffer issues otherwise. See not everything I say is good so I always test when I get a chance anyway.
January 8, 2003 at 8:28 am
Good point Antares686. I have also found that if you have a habbit of using the between statement and adding a date to your to_date, with the <= condition, you could encounter a little problem. If you have a column that does not use a time, it will return rows for the date you added 1 to.
January 9, 2003 at 4:47 am
Most of the time I have dates with no times so the problem is minimal. However on the odd occasion where I have times as well I have used both mromm's sql.
If I were to use Antares686's solution I would do it like
WHERE convert(char(10),dates,120) BETWEEN '2002-12-01' AND '2002-12-31'
I do all my sql date processing using yyyy-mm-dd format even if it requires a convert.
Edited by - davidburrows on 01/09/2003 04:47:31 AM
Far away is close at hand in the images of elsewhere.
Anon.
January 9, 2003 at 7:59 am
I tend to have times in my fields so I typically do a BETWEEN and convert my end date to a varchar with a time element of 23:59:59. Probably not the most efficient route if the column is indexed.
If there is an index, which one of these might be faster?
SELECT *
FROM t_too_many_dates
WHERE report_dt BETWEEN @start_dt AND DATEADD(dd, 1 @end_date)
AND report_dt <= CONVERT(DATETIME, CONVERT(VARCHAR(20), @end_dt, 1) + ' 23:59:59')
OR
SELECT *
FROM t_too_many_dates
WHERE report_dt BETWEEN @start_dt AND CONVERT(DATETIME, CONVERT(VARCHAR(20), @end_dt, 1) + ' 23:59:59')
Do you think the first query would use the index to get a working table and then just scan the results on the second clause?
January 9, 2003 at 10:18 am
I prefer to keep all comparisons as DATETIME values because of the formatting problems related to CHAR or VARCHAR. That being true, I tend to use variables like @beginDate and @endDate with the comparisons being WHERE column_date >= @beginDate AND column_date < @endDate
This assures me of the correct range. Since the BETWEEN operator includes the endpoints, you may accidentally get those upper end entries that have no time value.
PLUS - the conversion to a date with the time stripped is faster if you convert via numbers instead of strings.
Here is what I would do.
CREATE PROCEDURE FindInDateRange (
@DayInQuestion DATETIME
) AS
DECLARE
@beginDate SMALLDATETIME,
@endDate SMALLDATETIME
SELECT @beginDate = CONVERT( SMALLDATETIME, FLOOR( CONVERT( FLOAT, @DayInQuestion ) ) ),
@endDate = CONVERT( SMALLDATETIME, FLOOR( CONVERT( FLOAT, DATEADD( DAY, 1, @DayInQuestion ) ) )
SELECT <columns>
FROM <table>
WHERE DateCol >= @beginDate AND DateCol < @endDate
RETURN 0
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply