none
DateTime comparison

    Question

  • Hi,

    What is the best way to compare datetime in SQL?

    I am using:

    ---------------------------------------------------------------------------------------------
    ... @DT_AGENDAMENTO = 'Mar 10 2009 12:00:00:000AM'

    ....
    WHERE
    TB_PROVA.DT_AGENDAMENTO BETWEEN @DT_AGENDAMENTO + '00:00:00' AND @DT_AGENDAMENTO + '23:59:59' 
    ....
    ---------------------------------------------------------------------------------------------
    Which is working, I also try:

    ---------------------------------------------------------------------------------------------
    ....
    WHERE
    CONVERT(CHAR(10), TB_PROVA.DT_AGENDAMENTO, 103) = CONVERT(CHAR(10), @DT_AGENDAMENTO, 103)
    ....
    ---------------------------------------------------------------------------------------------
    Wich takes all the CPU of the machine...

    Is there a optimized way to compare a datetime field to a specific date (only date, not the time)?

    Thanks.


    MarcosGalvani
    Tuesday, March 10, 2009 5:43 PM

Answers

  • Marcos Galvani said:

    Ok,

    This:

    TB_PROVA.DT_AGENDAMENTO >= DATEADD([day], DATEDIFF([day], '19000101', @DT_AGENDAMENTO), '19000101')
    AND TB_PROVA.DT_AGENDAMENTO < DATEADD([day], DATEDIFF([day], '19000101', @DT_AGENDAMENTO) + 1, '19000101')

    worked fine... but this:
    TB_PROVA.DT_AGENDAMENTO >= @DT_AGENDAMENTO
      and TB_PROVA.DT_AGENDAMENTO < @DT_AGENDAMENTO

    don't.

    And just to make sure isn't the same if I did this?

    DATEDIFF([day], @DT_AGENDAMENTO, TB_PROVA.DT_AGENDAMENTO) = 0

    thanks.


    MarcosGalvani



    The problem with this approach is that when you manipulate the column that participate in the expression, you stop SQL Server from being able to use statistics on that column, or index where that column lead the key,  in order to estimate cardinality or number of rows that could match the expression.

    Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
    http://technet.microsoft.com/en-us/library/cc966419.aspx


    Example:

    USE Northwind;  
    GO  
     
    EXEC sp_helpindex N'dbo.Orders';  
    GO  
     
    SET SHOWPLAN_TEXT ON;  
    GO  
     
    SELECT OrderID, customerID, OrderDate  
    FROM dbo.Orders  
    WHERE DATEDIFF([day], OrderDate, '19960704') = 0;  
    GO  
     
    SET SHOWPLAN_TEXT OFF;  
    GO  
     
    SET SHOWPLAN_TEXT ON;  
    GO  
     
    SELECT OrderID, customerID, OrderDate  
    FROM dbo.Orders  
    WHERE OrderDate >= '19960704' AND OrderDate < '19960705';  
    GO  
     
    SET SHOWPLAN_TEXT OFF;  
    GO  
     

    There is an index on dbo.Orders by [OrderDate]. See the differences between both estimated execution plans.


    AMB
    Tuesday, March 10, 2009 6:23 PM
    Moderator

All replies

  • WHERE TB_PROVA.DT_AGENDAMENTO >= @DT_AGENDAMENTO
      and TB_PROVA.DT_AGENDAMENTO < @DT_AGENDAMENTO + 1


    by adding one day to the date and using the less than operator you allow the query to use an index on the DT_AGENDAMENTO column -- if one is avaiable.  Another alternative is:

    WHERE TB_PROVA.DT_AGENDAMENTO >= @DT_AGENDAMENTO
      and TB_PROVA.DT_AGENDAMENTO < dateadd(day, 1, @DT_AGENDAMENTO)



    Kent Waldrop Mr09


    Kent Waldrop Mr09
    Tuesday, March 10, 2009 5:58 PM
  • Marcos Galvani,

    Use this pattern.


    ... @DT_AGENDAMENTO = 'Mar 10 2009 12:00:00:000AM'

    ....
    WHERE
        
    TB_PROVA.DT_AGENDAMENTO >= DATEADD([day], DATEDIFF([day], '19000101', @DT_AGENDAMENTO), '19000101')
        
    AND TB_PROVA.DT_AGENDAMENTO < DATEADD([day], DATEDIFF([day], '19000101', @DT_AGENDAMENTO) + 1, '19000101')


    If we replace the values, it will look like:

    ....
    WHERE
        
    TB_PROVA.DT_AGENDAMENTO >= '20090310'
        
    AND TB_PROVA.DT_AGENDAMENTO < '20090311'


    AMB

    Tuesday, March 10, 2009 6:00 PM
    Moderator
  • Alejandro's version of the WHERE has the advantage that hs WHERE clause provides the ability to strip time out of the @DT_AGENDAMENTO variable whereas the WHERE clauses that I supplied assumed that the @DT_AGENDAMENTO has no TIME component (other than possibly midnight).


    Kent Waldrop Mr09
    Tuesday, March 10, 2009 6:07 PM
  • Marcos,

    Exactly as Kent stated. I wouldn't have posted my msg if I would have seen Kent's one.


    AMB
    Tuesday, March 10, 2009 6:18 PM
    Moderator
  • Ok,

    This:

    TB_PROVA.DT_AGENDAMENTO >= DATEADD([day], DATEDIFF([day], '19000101', @DT_AGENDAMENTO), '19000101')
    AND TB_PROVA.DT_AGENDAMENTO < DATEADD([day], DATEDIFF([day], '19000101', @DT_AGENDAMENTO) + 1, '19000101')

    worked fine... but this:
    TB_PROVA.DT_AGENDAMENTO >= @DT_AGENDAMENTO
      and TB_PROVA.DT_AGENDAMENTO < @DT_AGENDAMENTO

    don't.

    And just to make sure isn't the same if I did this?

    DATEDIFF([day], @DT_AGENDAMENTO, TB_PROVA.DT_AGENDAMENTO) = 0

    thanks.

    MarcosGalvani
    Tuesday, March 10, 2009 6:18 PM
  • And another question is why is 

    TB_PROVA.DT_AGENDAMENTO BETWEEN @DT_AGENDAMENTO + '00:00:00' AND @DT_AGENDAMENTO + '23:59:59' 

    faster than all other options mentioned in this tread?

    regards.
    MarcosGalvani
    Tuesday, March 10, 2009 6:23 PM
  • Marcos Galvani said:

    Ok,

    This:

    TB_PROVA.DT_AGENDAMENTO >= DATEADD([day], DATEDIFF([day], '19000101', @DT_AGENDAMENTO), '19000101')
    AND TB_PROVA.DT_AGENDAMENTO < DATEADD([day], DATEDIFF([day], '19000101', @DT_AGENDAMENTO) + 1, '19000101')

    worked fine... but this:
    TB_PROVA.DT_AGENDAMENTO >= @DT_AGENDAMENTO
      and TB_PROVA.DT_AGENDAMENTO < @DT_AGENDAMENTO

    don't.

    And just to make sure isn't the same if I did this?

    DATEDIFF([day], @DT_AGENDAMENTO, TB_PROVA.DT_AGENDAMENTO) = 0

    thanks.


    MarcosGalvani



    The problem with this approach is that when you manipulate the column that participate in the expression, you stop SQL Server from being able to use statistics on that column, or index where that column lead the key,  in order to estimate cardinality or number of rows that could match the expression.

    Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
    http://technet.microsoft.com/en-us/library/cc966419.aspx


    Example:

    USE Northwind;  
    GO  
     
    EXEC sp_helpindex N'dbo.Orders';  
    GO  
     
    SET SHOWPLAN_TEXT ON;  
    GO  
     
    SELECT OrderID, customerID, OrderDate  
    FROM dbo.Orders  
    WHERE DATEDIFF([day], OrderDate, '19960704') = 0;  
    GO  
     
    SET SHOWPLAN_TEXT OFF;  
    GO  
     
    SET SHOWPLAN_TEXT ON;  
    GO  
     
    SELECT OrderID, customerID, OrderDate  
    FROM dbo.Orders  
    WHERE OrderDate >= '19960704' AND OrderDate < '19960705';  
    GO  
     
    SET SHOWPLAN_TEXT OFF;  
    GO  
     

    There is an index on dbo.Orders by [OrderDate]. See the differences between both estimated execution plans.


    AMB
    Tuesday, March 10, 2009 6:23 PM
    Moderator
  • Does the optimizer place a higher priority on the between operator over the inequalities?  I have certainly seen that behavior before in DB2.  I will dig a little; Alejandro might know this one without digging?


    Kent Waldrop Mr09
    Tuesday, March 10, 2009 6:40 PM
  • Is there a way to compare using the first 32 bits of datetime field? If it is possible to access this information directly, withouy having to use date functions I believe the comparison would be much faster.

    thanks.


    MarcosGalvani
    Tuesday, March 10, 2009 6:41 PM
  • What are the datatypes of @DT_AGENDAMENTO and the DT_AGENDAMENTO column?

    Kent Waldrop Mr09
    Tuesday, March 10, 2009 6:53 PM
  • The datatype for both are datetime.
    MarcosGalvani
    Tuesday, March 10, 2009 7:05 PM
  • Marcos,

    why don't you tell us a little bit more about how are you testing this and estructure of the table involved, including constraints and indexes.

    - Post information about the table, including constraints and indexes
    - Post the exact select statement you are trying to execute
    - Tell us how are you executing the statement. Are you using an stored procedure or are you executing it as an adhoc query?

    The pattern we gave to you have been used and proved for long time now.

    AMB
    Tuesday, March 10, 2009 7:47 PM
    Moderator
  • Kent Waldrop said:

    Does the optimizer place a higher priority on the between operator over the inequalities?  I have certainly seen that behavior before in DB2.  I will dig a little; Alejandro might know this one without digging?


    Kent Waldrop Mr09



    Kent,

    The BETWEEN operator is translated as ">= and <=".


    AMB
    Tuesday, March 10, 2009 7:48 PM
    Moderator
  •  

    Hunchback

    I really don't want to give you all this trouble. I see the trick if the statement

    TB_PROVA.DT_AGENDAMENTO >= @DT_AGENDAMENTO
      and TB_PROVA.DT_AGENDAMENTO < @DT_AGENDAMENTO

    we have to use a dateadd([day], 1, @DT_AGENDAMENTO) on the last AND clause.

    I was just wondering how to speed you the where clauses that use datetime fields. You already gave me a lot of useful information, specially the YYYYMMDD string format. So I can use it without worry about the internal conversion. As I understood it will always be converted right.

    Thanks for all your help.

    MarcosGalvani
    Tuesday, March 10, 2009 7:58 PM