Results 1 to 11 of 11

Thread: Magic Date?

  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Magic Date?

    Anyone know why 1899-12-30 is a special date?

    If you put that date with a time in sql server EM and a vb call will return only the time portion...

    More background and sample code

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30709
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    In vb, the date is a floating-point where the integer portion is the date. 12/30/1899 is the base date. So if you enter a time only, the integer portion of the datetime will be 0 and the decimal portion will be the time.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's EM Built in? If you just enter a time through EM, it'll ne 1899-12-30...

    And isn't 1900-01-01 the 0 date for sql server?

    SELECT CONVERT(datetime,0,101)

    ?????

    And why, if you call sql server from vb through ado, does it pass back just the time component...with no conversion function?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I wonder if there is a magic time, that just returns the date...

    Why the original developer didn't use CONVERT is betond me....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    Do a query against that datetime column that only has a time and add 1 to it - your question will be answered.

  6. #6
    Join Date
    Sep 2003
    Posts
    212
    SELECT CONVERT(datetime,-2,101)
    gives '1899-12-30 00:00:00.000'

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by rnealejr
    Do a query against that datetime column that only has a time and add 1 to it - your question will be answered.
    What does that mean?

    Datetime is stored as a number...4 before the decimal, 4 after...

    What do you mean time only?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    So what does this prove?

    SELECT DATEADD(d,1,CONVERT(datetime,0.1))


    Add 1 what?

    What do you mean with no date? There's always a date component?

    0 is the default

    (Unless of course you add through EM then it's -2)

    huh?

    And why, if you make a sql call from vb, and the date is 1899-12-30, it only returns the time..VERY bizzare
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just had the developer do it from an Excel workbook too.

    It puts just the time in the Cell with that 1899-12-03 date

    It doesn't return the date...bizzaro

    Anyone else seen this?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27101

    Hey Brett ... some more with excel problems ...

    I believe the reason is that while SQL takes the default date to be 1900-01-01

    and other MS applications use 1899-12-30

    Here is a link
    http://msdn.microsoft.com/library/de...itgetthere.asp
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Thanks...forgot all about that thread....

    Still doesn't explain you only get the decimal portion of the datetime field though (that's the time component)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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