kw_uh97
asked on
How do I compare DateTime using SQL more efficiectly?
Hello everyone
I have a query that has a where clause and in my where clause I am using
WHERE DATEPART(mm, [CreateDate]) = DATEPART(mm, '8/01/2007)
AND DATEPART(yyyy, [CreateDate]) = DATEPART(yyyy, '8/01/2007)
I would like to shorten my query by using less code. Is there a way I can do the same thing with less code?
Thanks for any assistance
I have a query that has a where clause and in my where clause I am using
WHERE DATEPART(mm, [CreateDate]) = DATEPART(mm, '8/01/2007)
AND DATEPART(yyyy, [CreateDate]) = DATEPART(yyyy, '8/01/2007)
I would like to shorten my query by using less code. Is there a way I can do the same thing with less code?
Thanks for any assistance
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks AW;
Miriam, applying the CONVERT function to DATETIME precludes the use of indexes to improve performance. From a performance standpoint, this is better for a single date:
Where [CreateDate] >= '20070801' --good tip dportas
and [CreateDate] < '20070802'
Miriam, applying the CONVERT function to DATETIME precludes the use of indexes to improve performance. From a performance standpoint, this is better for a single date:
Where [CreateDate] >= '20070801' --good tip dportas
and [CreateDate] < '20070802'
>Thanks for the respone but sorry, my compare date is only '8/1/2007:' I am not comparing '9/1/2007'. This was not a typo. There is not a >= or <=. Thanks for any assitance.
well, you want all the records that are in the month 2007-08, yes or no?
so, if you insist on only using that value, we could rewrite the efficient query like that:
WHERE [CreateDate] >= CONVERT(DATETIME, '08/01/2007', 101)
AND [CreateDate] < dateadd(month, 1, CONVERT(DATETIME, '08/01/2007', 101))
and also here, the >= and the < are not a type neither.
the shorter version would be like this:
WHERE CONVERT(VARCHAR(7), [CreateDate], 120) = CONVERT( VARCHAR(7), CONVERT(DATETIME, '08/01/2007', 101), 120)
well, you want all the records that are in the month 2007-08, yes or no?
so, if you insist on only using that value, we could rewrite the efficient query like that:
WHERE [CreateDate] >= CONVERT(DATETIME, '08/01/2007', 101)
AND [CreateDate] < dateadd(month, 1, CONVERT(DATETIME, '08/01/2007', 101))
and also here, the >= and the < are not a type neither.
the shorter version would be like this:
WHERE CONVERT(VARCHAR(7), [CreateDate], 120) = CONVERT( VARCHAR(7), CONVERT(DATETIME, '08/01/2007', 101), 120)
ASKER