August 29, 2014 at 3:50 am
Hi!
I have a datetime column, to which I just need to compare the date part.
Which is the way that performs better to get the rows with a specific date, regardless the time?
Is this a good solution?
DECLARE @DateToFind DATETIME = '2011-11-07 14:40:42.237'; -- (example date)
SELECT Column1, Column2 FROM MyTable
WHERE
DateColumn >= CAST(@DateToFind AS DATE) AND
DateColumn < DATEADD(DAY,1,CAST(@DateToFind AS DATE))
Thanks in advance!
August 29, 2014 at 5:30 am
The example posted is fine. Virtually any method where you're performing the conversion against the parameter will be trivial to the overall runtime as they only have to be evaluated once.
What you need to avoid (and have avoided with this example) is running a conversion against the column to compare with a parameter as it will prevent index use* (except where there's an exact match against a computed column) and make statistics nearly impossible to estimate.
*the exception to this is from 2008 onwards, if you use Cast(column as date), there's actually a special optimisation that converts this to a SARGable range
August 29, 2014 at 7:29 am
HowardW, thanks for your explanation!
You've confirmed my thoughts.
Another question, (sorry for join two questions in one topic)
Instead of a datetime, if I cast a string with format 'yyyy-MM-dd',
will this cast always result in the correct date regardless the collation or other server parameters? (considering this string matching with a valid date)
I'm using this because I can't pass a datetime parameter from c# to sql as a datetime format (as I can with a stored procedure), so I convert to string an then to date.
CREATE FUNCTION [dbo].[MySqlFunctionByDate] (@DateToFindStr varchar(20))
RETURNS TABLE
AS
RETURN
(
SELECT Column1, Column2 FROM MyTable
WHERE
DateColumn >= CAST(@DateToFindStr AS DATE) AND
DateColumn < DATEADD(DAY,1,CAST(@DateToFindStr AS DATE))
)
try
{
using (SqlConnection con = new SqlConnection(myConnectionString))
{
string command = string.Format("SELECT * FROM MySqlFunctionByDate('{0}')", date.ToString("yyyy-MM-dd HH:mm:ss"));
using (SqlDataAdapter da = new SqlDataAdapter(command, con))
{
DatasetDS ds= new DatasetDS();
da.Fill(ds.Dataset);
return ds;
}
}
}
catch (Exception ex)
{
//handle error
}
Thanks!
August 29, 2014 at 7:37 am
You don't need to be using a Stored Proc to pass properly typed parameters.
Follow the example here:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx
Basically, refer to the parameter using normal @VariableName syntax in your CommandText, then add parameters to the SqlCommand before executing
August 29, 2014 at 7:53 am
amns (8/29/2014)
Hi!I have a datetime column, to which I just need to compare the date part.
Which is the way that performs better to get the rows with a specific date, regardless the time?
Is this a good solution?
DECLARE @DateToFind DATETIME = '2011-11-07 14:40:42.237'; -- (example date)
SELECT Column1, Column2 FROM MyTable
WHERE
DateColumn >= CAST(@DateToFind AS DATE) AND
DateColumn < DATEADD(DAY,1,CAST(@DateToFind AS DATE))
Thanks in advance!
This will also work and will still use an index on DateColumn is it exists:
select
Column1,
Column2
from
dbo.MyTable
where
cast(DateColumn as DATE) = cast(@DateToFind as DATE);
Give it a try and see.
August 29, 2014 at 8:09 am
HowardW - Thanks for your tip!
Lynn Pettis
Will this
cast(DateColumn as DATE) = cast(@DateToFind as DATE);
August 29, 2014 at 8:14 am
amns (8/29/2014)
HowardW - Thanks for your tip!Lynn Pettis
Will this
cast(DateColumn as DATE) = cast(@DateToFind as DATE);
???
August 29, 2014 at 8:27 am
Sorry, I clicked enter before ending writing and I didn't realize... :s
HowardW - Thanks for your tip!
Lynn Pettis
Will this:
WHERE cast(DateColumn as DATE) = cast(@DateToFind as DATE);
perform better than this?
WHERE DateColumn >= CAST(@DateToFind AS DATE) AND DateColumn < DATEADD(DAY,1,CAST(@DateToFind AS DATE))
With your suggestion a conversion against all rows of DateColumn will be performed.
Thanks!
August 29, 2014 at 8:41 am
The difference between the two would be trivial for any meaningful dataset as they are both SARGable.
As I mentioned in my first post, this is a special case for casting the column as a date - most other conversions that are applied to the column to remove the time part would render the predicate non-SARGable. So as a general rule, it's better to try converting the parameters before thinking about converting the column.
August 29, 2014 at 9:33 am
HowardW is correct. The only reason I mentioned the CAST(DateColumn as DATE) alternative is because it is still SARGable. I had to prove it to myself a while back when I saw someone else do it in one of the forums on SSC.
August 29, 2014 at 10:16 am
amns (8/29/2014)
Instead of a datetime, if I cast a string with format 'yyyy-MM-dd',
will this cast always result in the correct date regardless the collation or other server parameters? (considering this string matching with a valid date)
No. For a literal date in SQL Server, always use format 'YYYYMMDD' because that is 100% interpreted correctly, regardless of any SQL setting.
'YYYY-MM-DD', otoh, can fail or cause data errors, because SQL may mistakenly think it is yyyy-dd-mm instead, for example, in Europe.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 29, 2014 at 10:47 am
Thanks for your help!
September 1, 2014 at 12:06 pm
ScottPletcher (8/29/2014)
amns (8/29/2014)
Instead of a datetime, if I cast a string with format 'yyyy-MM-dd',
will this cast always result in the correct date regardless the collation or other server parameters? (considering this string matching with a valid date)
No. For a literal date in SQL Server, always use format 'YYYYMMDD' because that is 100% interpreted correctly, regardless of any SQL setting.
'YYYY-MM-DD', otoh, can fail or cause data errors, because SQL may mistakenly think it is yyyy-dd-mm instead, for example, in Europe.
Can you provide example where YYYY-DD-MM would be used in Europe?
I have never seen such format. We have a lot of DD.MM.YYYY though.
Could not find such from http://msdn.microsoft.com/en-us/library/ms187928.aspx either.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply