Rajesh Kumar wrote:
> Peter Brawley unknowingly asked us:
>>> Interestingly, use of date_add() and date_sub() on 'odd' dates such
>>> as "Feb 31" does produce sane results.
>>> Subtract one from "2000 Feb 31", and you'll get 2000-03-01.
>> This is sane!!??
> This is where Unix Timestamps come into action (and perhaps rescue)!
> To be sure that we're storing a sane value in our DB, we could use:
> SELECT unix_timestamp('2002-02-31');
> which provides a correct result.
> Some interesting results:
> We could then probably eliminate all timestamps stored as 1969-12-31
> as we know that they're illegal.
Uh, isn't it an signed integer, thus allowing date before 1970-01-01 too??
Anyway, all this led me towards is:
create database data_test ;
use data_test;
create table test3 (a date);
insert into test3 values (
from_unixtime(unix_timestamp('2002-1111102-31'),'%Y-%m-%d' ));
insert into test3 values (
from_unixtime(unix_timestamp('1996-02-31'),'%Y-%m-%d' ));
insert into test3 values (
from_unixtime(unix_timestamp('1996-67-31'),'%Y-%m-%d' ));
insert into test3 values ( from_unixtime(-2147483646));
select * from test3;
still resulting in a mash:
1970-01-01
1996-03-01
1970-01-01
1900-01-00
that comes with neither warnings nor errors reported ...
I don't like to organise my dates this way ...
HansH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
