April 4, 2008 at 4:22 am
when you are fetching data for a specific date, it is always advisable to get the input parameter in varchar(10), i.e., in mm/dd/yyyy format, or atleast convert the input datetime parameter into the same. Then you can have your where clause with a conversion of the date field into varchar(10) and match it with the variable for the parameter. This will avoid irrelevant data being fetched. For example, it might fetch data for the next date. A datetime variable on the other hand is necessary for a between case.
Note: You can also use between 'mm/dd/yyyy 00:00' and ''mm/dd/yyyy 23:59'
others please confirm.
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
April 4, 2008 at 8:32 am
Whenever possible, you should avoid putting 'functions' in the 'WHERE' clause. Things like 'convert', 'datediff', 'upper', etc.
It isn't a problem for small datasets, but can be for large datasets. Reason is it can cause a table or index scan rather rather than index seek.
So in our example, using the '>= and <' construct, we avoid that and still get the results we want.
Also remember that 'datetime' and 'smalldatetime' can store values out in the milliseconds...'2008-04-04 10:21:16.630'...this occurs all the time if you used 'getdate()' to store the date.
So if you are going use 'BETWEEN' and 'build' the before and after dates, you have to take the milliseconds into account....using '>= and <' construct removes this potential error as well.
Why take a chance on a small error when it is very easy to have NO error. In my experience, just when you say 'that will never happen', it usually does happen.
Hope this helps!
[edited >]
If it was easy, everybody would be doing it!;)
April 4, 2008 at 8:56 am
Trader Sam (4/4/2008)
Whenever possible, you should avoid putting 'functions' in the 'WHERE' clause. Things like 'convert', 'datediff', 'upper', etc.It isn't a problem for small datasets, but can be for large datasets. Reason is it can cause a table or index scan rather rather than index seek.
So in our example, using the '>= and <' construct, we avoid that and still get the results we want.
Also remember that 'datetime' and 'smalldatetime' can store values out in the milliseconds...'2008-04-04 10:21:16.630'...this occurs all the time if you used 'getdate()' to store the date.
So if you are going use 'BETWEEN' and 'build' the before and after dates, you have to take the milliseconds into account....using '>= and <' construct removes this potential error as well.
Why take a chance on a small error when it is very easy to have NO error. In my experience, just when you say 'that will never happen', it usually does happen.
Hope this helps!
[edited >]
great post many thanks
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply