>Stas writes:
> > Hi. I have a problem with timestamp(4) data type. It doesnot work, if i
> > write '9901' for example, it has '0000'. The timestamp(6) works correctly.
> >
> > And it will be very good, if you will add data type such timestamp(5) -
> > YYYYMM.
> >
> > Stas
> > [EMAIL PROTECTED]
> >
>
>Hi!
>
>TIMESTAMP should be 12, 8 or 6. 4 can not work correctly.
Actually, TIMESTAMP(4) is documented as having a display format of YYMM.
And it does. But you cannot *insert* YYMM values as you might think.
The value you insert must be a legal date, and YYMM values are not
legal dates.
If you insert 990101, then the value retrieved will indeed be 9901.
What's happening when you insert '9901', I *think*, is that MySQL
interprets 99 as the years and 01 as the months. Still, the days part
is missing (i.e., 0), and 990100 isn't a legal date, so you get 0 as
the value that is stored.
The relevant section of the manual is 7.2.3 "Date and time types",
which says that only the formats described there are supported,
and that unpredictable results may occur if you use values in other
formats.
Later on, it says:
---------------
Values specified as numbers should be 6, 8, 12 or 14 digits long.
If the number is 8 or 14 digits long, it is assumed to be in `YYYYMMDD'
or `YYYYMMDDHHMMSS' format and that the year is given by the first 4
digits. If the number is 6 or 12 digits long, it is assumed to be in
`YYMMDD' or `YYMMDDHHMMSS' format and that the year is given by the
first 2 digits. Numbers that are not one of these lengths are
interpreted as though padded with leading zeros to the closest length.
Values specified as non-delimited strings are interpreted using
their length as given. If the string is 8 or 14 characters long, the
year is assumed to be given by the first 4 characters. Otherwise the
year is assumed to be given by the first 2 characters. The string is
interpreted from left to right to find year, month, day, hour, minute
and second values, for as many parts as are present in the string.
---------------
Maybe that's a bit unclear, because the second paragraph doesn't explicitly
*rule out* 2- or 4-character string values. Maybe something to that
effect should be added. Monty, any comment?
--
Paul DuBois, [EMAIL PROTECTED]
Northern League Chronicles: http://www.snake.net/nl/
Madison Black Wolf: http://www.primate.wisc.edu/people/dubois/blackwolf/
-----------------------------------------------------------
Send a mail to [EMAIL PROTECTED] with
unsubscribe mysql [EMAIL PROTECTED]
in the body of the message to unsubscribe from this list.
