• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4101
  • Last Modified:

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
5 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
less code... well not really.

this version would be able to take into consideration an index on field CreateDate:
WHERE [CreateDate] >= CONVERT(DATETIME, '08/01/2007', 101)
AND [CreateDate] < CONVERT(DATETIME, '09/01/2007', 101)

shorted would be this:
WHERE CONVERT(VARCHAR(7), [CreateDate], 120) = '2007-08'

WHERE [CreateDate] >= '08/01/2007'
    and [CreateDate] < '09/01/2007'

will be much faster, especially if  you have an index on CreateDate.

WHERE CreateDate >= '20070801' AND CreateDate < '20070901'

The 'YYYYMMDD' format for date literals is "safe" irrespective of date format and language settings. Avoid any expressions based on the date column otherwise the server will have to compute them for each row and will be less able to take advantage of any indexes.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

kw_uh97Author Commented:
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.
The suggestions are checking to see if the CreateDate is any day in the Month of August 2007.  The fact that they utilized the first of September is simply to make sure that the CreateDate is on or AFTER 8/1/2007 (>= '8/1/2007'), but simultaneously BEFORE 9/1/2007 (< '9/1/2007').  You code is testing ALSO if the MonthPart of the date is 8 (August), and the year part of the date is 2007 (which means that you are ALSO testing if the CreateDate is any date in the Month of August, 2007.  All of the suggesed alternatives ar testing EXACTLY the same thing that your code is testing, and the offered suggestions are MUCH easier to understand, an will, in fact., asll execute FASTER than your original code.

The code you have only checks Month and year, which is why everyone assumed you want anything in August 2007.  If you just want that one date, this will work

 CONVERT(DATETIME, [CreateDate], 101) = '08/01/2007'

If you want anything in August 2007, see other posts or
year ([createdate]) = 2007
and month (createdate]) = 8
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'

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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)


Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now