Results 1 to 6 of 6

Thread: DATETIME range

  1. #1
    Join Date
    Jun 2002
    Posts
    63

    Unanswered: DATETIME range

    Hi,

    Looking at the MySQL documentation for the DATETIME data type it clearly states that the valid range is '1000-01-01' to '9999-12-31'. However, I found that it was possible to insert the value 0001-00-00 00:00:00 - this is clealy out of the designated range.

    (MySQL :: MySQL 3.23, 4.0, 4.1 Reference Manual :: 10.3.1 The DATETIME, DATE, and TIMESTAMP Types)

    My guess is that this has something to do with server modes - but I can't seem to work out how to enforce this range. I tried sql-mode=NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_ALL_TABLE and whilst this stopped me from having zeros in the date in did not enforce the range.

    Any ideas?

    thanks


  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're right, it does seem to allow the value '0001-00-00' to be entered

    however, notice the actual wording in the documentation:
    Quote Originally Posted by da Manual
    For the DATETIME and DATE range descriptions, “supported” means that although earlier values might work, there is no guarantee.
    as for how to actually enforce the range, you're probably going to have to do this with application code, since mysql doesn't (yet) support CHECK constraints

    what is the semantic meaning of your date? what actual range were you interested in enforcing?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2002
    Posts
    63
    Thanks for the response.

    I read that scentence round the other way i.e. I thought it meant: the range is X to Y and earlier versions might have a smaller range. Whereas I think you read it to mean: the range is X to Y but the range may be differ dependent on the version. Anyway, I think I'm reading into this too much

    I wasn't really asking because of a concrete problem. I'm in the process of writing a PHP article about dates and time zones so wanted to quote the valid range for this data type.

    I wonder if the range is affected by the engine being used? If so it would make sense that the quoted range would be for the lowest common denomenator.

  4. #4
    Join Date
    Jun 2002
    Posts
    63
    As a arandom note - just found this: Supported Data Types and Values | ICE Wiki

    I know infobirght isn't a standard engine, but this list does show that engines may have different defintions of data types.

    Unfortunatley I haven't managed to find equivalent lists for the standard engines

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mythix
    Whereas I think you read it to mean: the range is X to Y but the range may be differ dependent on the version.
    i read it to mean the range is X to Y but earlier values of X might work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2002
    Posts
    63
    ahhhhh - I misread it

    values! not versions!

    thanks - sorry for dumbness

Posting Permissions

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