SQL Server Performance

Caluclate Date part difference between two dates with out using datediff function

Discussion in 'T-SQL Performance Tuning for Developers' started by mmkrishna1919, Jun 25, 2012.

  1. mmkrishna1919 New Member

    Hi All,
    i have an T sql query in this where clause contain one condition like
    where datediff(dd,start_date,end_date)>0
    my assumption is if we use functions on the columns then indexes on those columns are not used by the optimiser,hence can anyone suggest how to find date part exact difference by including indexes,with out using date functions?
    mmkrishna1919, Jun 25, 2012
    #1

  2. Madhivanan Moderator

    where datediff(dd,start_date,end_date)>0

    can be converted to

    where end_date>start_date

    Isn't it? :)
    Madhivanan, Jul 10, 2012
    #2

  3. FrankKalis Moderator

    Not really. At least not if DATETIME data types are used. :)
    Your condition will be met for all end_date > start_date even if they are on the same day, which is not how I read it. I think he want to know those rows where the end_date is at least 1 day after the start_date (without the time, of course). So, something like this might do:

    Code:
    end_date >= DATEADD(DAY, DATEDIFF(DAY, 0, start_date), 1)
    If, however, he is using a DATE type, your condition is fine.
    FrankKalis, Jul 10, 2012
    #3

  4. Madhivanan Moderator

    But you haven't specified start_date in your condition :)
    Madhivanan, Jul 10, 2012
    #4

  5. FrankKalis Moderator

    aargh.. good point, well presented. Changed.
    FrankKalis, Jul 10, 2012
    #5

Share This Page