Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004

    Unanswered: How does addition of two datetime data types work ?

    SQL Server allows addition of two "datetime" values. For example let d1='April 10,2004' and d2='April 11, 2004'. Now when I execute d1+d2 I get 2108-07-20 00:00:00.000. On what logic is this calculation done.

    I am actually looking to derive the same result in Oracle. Hence wanting to know the funda behind this.

    many thanks.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    this is based on the fact that each datetime value in sql server is actually stored as a pair of integers

    the first integer stores the number of days after 1900-01-01, and the second integer stores the number of milliseconds since midnight

    when you add two dates, you are really just adding their integer values

    check this out:
    PHP Code:
    declare @d1 datetime
    declare @d2 datetime

    select @d2='2004-04-11'

    select convert(char(10),@d1,120
    cast(@d1 as integer)
    cast(@d2 as integer)
    cast(@d1+@d2 as integer)

    2004-04-10 2004-04-11 2108-07-20 38085 38086 76171 
    to do this in another database, and to achieve the same 2108-07-20 result, you may have to to do the conversion to integer yourself

    be careful, other databases may use a different base date than 1900-01-01 | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Thanks for the help. I am clear on the logic now.

Posting Permissions

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