Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: date vs datetime

    Hi all,

    Quite a broad question but it occurred to me today that I don't know the answer.

    Is there a performance impact (if so, how great is it) when comparing a date to a datetime data type?

    An educated guess suggests yes, as there will be a type casting... but then again I don't believe there's an issue when comparing an int to a tinyint and there's an assumption these would play by the same rules?

    This has only come about because I'm considering changing the data type used in my standard calendar script and this popped in to my head.

    Will happily accept one word answers
    George
    Home | Blog

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Why? .....

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Depends.....

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    OK, I guess I can give a longer answer. The problem comes about when you have implicit conversions. In earlier versions of SQL Server (< SQL 2005), the rule was the argument with the lesser domain would be converted to the datatype of the argument with the greater domain. So, if you compared an nvarchar value vs a varchar value, the varchar value would be converted to an nvarchar. Even if that meant converting a column of millions of values.

    Things have changed, and it appears that the system tries to convert the scalar value much more often, even if it breaks the previous rule. Observe (tested on SQL 2012)

    Code:
    create table test1
    (col1 int,
     col2 date,
     col3 datetime)
    
     declare @i int = 0
    
     while @i < 10000
       begin
         insert into test1
    	 values (@i, getdate() + @i, getdate() + @i)
    
         set @i += 1
       end
    
    create index ind2 on test1 (col2)
    create index ind3 on test1 (col3)
    
    declare @test2 date
    declare @test3 datetime
    
    select @test2 = getdate(), @test3 = getdate()
    
    select *
    from test1
    where col3 = @test2
    
    select *
    from test1
    where col2 = @test3
    The test plans for both select * queries are the same (albeit with different indexes), but there is no implicit conversion. Both will give no results, too, so when the date column is compared to an argument with a greater domain (datetime), the system is not just truncating the time portion off of the scalar value to make things fit.

    I think this also works for bigint-int-smallint comparisons, as well. I remember clearly when I tried to show why a developer should have his datatypes agree, my standard demonstration script gave me quite unexpected results. I have not tested recently with varchar vs nvarchar. That is left as an exercise for the interested reader.
    Last edited by MCrowley; 12-18-13 at 14:38. Reason: Fixed a typo.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    For comparison purposes (scalar versus scalar) the difference is trivial on all but the smallest hardware.

    If the type conversion happens to invalidate an important index used by a seek or a join, then the difference can be catastrophic. I tried to create a simple example of this and failed, which means I have something else to entertain me some evening!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Cheers guys. Have been dragged off on to other work today so will have a play with this tomorrow (hopefully!).
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •