How do I  compare DateTime using SQL more efficiectly?

Posted on 2007-08-11
Last Modified: 2013-11-05
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
Question by:kw_uh97
    LVL 142

    Accepted Solution

    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'

    LVL 42

    Assisted Solution

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

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

    Assisted Solution


    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.

    Author Comment

    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.
    LVL 44

    Assisted Solution

    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.

    LVL 2

    Assisted Solution

    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
    LVL 42

    Expert Comment

    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'

    LVL 142

    Expert Comment

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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now