DATETIME range

mythix · #1 (**permalink**) 10-02-09, 06:17

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

r937 · #2 (**permalink**) 10-02-09, 08:59

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?

mythix · #3 (**permalink**) 10-02-09, 09:29

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.

mythix · #4 (**permalink**) 10-02-09, 09:55

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