Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: Insert statement for datetime column fails

    I ve a simple table with a column of type datetime. I ve successfully inserted the following values in it,

    2006-09-13 18:00:10
    2006-09-14 18:00:10
    2006-09-15 18:00:10


    however, it fails when i try to insert the value 0000-00-00 00:00:00. ie., the following insert statement fails

    Code:
    INSERT INTO TEST VALUES('0000-00-00 00:00:00')
    The error thrown is,

    Code:
    Server Msg 242, Level 16,  State 3, Line 1 
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Obviously SQL Server thinks that '0000-00-00 00:00:00' is not a valid date and I couldn't agree more.
    You should use NULL to "mark" a column's value as absent, not some strange (invalid) value

  3. #3
    Join Date
    Apr 2005
    Posts
    127
    hi shammat,

    thanks for the reply.. whats the least possible valid day i could enter for a datetime column..

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    What's wrong with NULL?

    Edit:

    The lowest value is documented in the manual:
    http://msdn2.microsoft.com/en-us/library/ms187819.aspx

  5. #5
    Join Date
    Apr 2005
    Posts
    127
    Hi shammat,

    I ve migrated an existing table structure and its data from MySQL to SQLServer, in MySQL the column is Not NULL and one of the row has the value 0000-00-00 00:00:00. When i tried to create the same in SQLServer i faced such errors..

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    This is something I have seen a lot recently.
    Why would anybody declare a column as NOT NULL and then put a totally meaningless value in there, just to comply with the NOT NULL constraint.

    That sure does not make any sense to me

    I do understand that this was not your decision, I'm just wondering why people do such stupid things

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    shammat, very nicely stated, i totally agree

    the fact that mysql allows a "zero date" sure detracts from its reputation

    the fact that mysql programmers would actually utilize it detracts from them even more
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    To be fair against the MySQL users:
    I have seen this in an Oracle environment as well, they simply used 1970-01-01 instead...

    But then - I have seen it only once with Oracle, whereas I tend to see it more often in the MySQL area

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've always been in favor of using a NULL when possible to mark data with an unknown or an unknowable value, but many systems can't cope with that due to the programming language being used.... Many COBOL variants just don't cope with NULL very gracefully, and a number of "4GL" wannabes have the same problems, although they are dressed up in newer clothes.

    In defense of the SQL Server choice for minimum date, that wasn't truly their choice... They had to deal with calendar reformations, and simply picked the earliest date that wasn't likely to have problems for most users. The Julian to Gregorian conversion was messy, it wasn't implemented the same way in many places, and wasn't implemented at the same time everywhere... We take it for granted that only timezones need to be considered to determine when 2006-11-01 will occur because the world has only had a couple of calendars since 1800, and all of those calendars conveniently convert to the Gregorian. This has not been the case throughout history.

    -PatP

  10. #10
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by Pat Phelan
    And a number of "4GL" wannabes have the same problems, although they are dressed up in newer clothes.
    Understable, but still not nice

    In defense of the SQL Server choice for minimum date, that wasn't truly their choice...
    I find the minimum date to be perfectly fine, as a matter of fact a lot better than allowing 0000-00-00.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the minimum date is not "perfectly fine"

    it may be practicable, but it does introduce another form of "three-valued logic"

    for instance, if you assign the minimum date to a date_of_birth column in those instances when you do not know the person's date_of_birth, you cannot simply go blithely ahead and calculate the person's current age

    well, technically speaking, you could, but it would be wrong

    so using the minimum date is far from "perfect"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by r937
    the minimum date is not "perfectly fine"
    I didn't mean that the usage of it was fine. I totally agree with you that it is nonsens to use special values for this purpose

    I meant the restriction for a minimum date value is acceptable. A valid date as the minimum date is "perfectly fine" compate to 0000-00-00

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I just wonder how much code relies of that date "not being there" as 0000-00-00.....
    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.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    I just wonder how much code relies of that date "not being there" as 0000-00-00.....
    exactly

    and if you should ever need to move the app to another database platform? code changes!!

    nothing more fun than coming in to the office on a sunny saturday afternoon to find all occurrences of 0000-00-00 and replace them with 1970-01-01

    whereas if you had used NULL in the first place...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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