Date format problem

poojajt · #1 (**permalink**) 12-08-03, 03:47

Hi,
whenever i try to enter a date it acts weird and gives me some stupid value...when i went thru' the MYSQL site the format as per them is YYYY-MM-DD and i tried to enter the row value in the same format but everytime i change the value it gives something else......
this is the statement in the create table statement for defining this field...........
Mail_received_date date

i tried the follwoing statement with different values 'n it gives me a new date inserted everytime...

update mail set mail_received_date=12/3/2003 where mail_id ='1';

date value inserted by me ------- actual date inserted
12/3/2003 --------- 2000-00-20
2003-02-11 -------- 0000-00-00
03/11/2003 --------- 2000-00-01
31/11/2003 --------- 2000-00-14
31/01/2003 ---------- 0000-00-00
31/10/2003 ----------- 2000-00-15
2031/10/2003 ---------- 2000-10-14
02112003 ---------- 0000-00-00
2031/2010/2003 ---------- 2000-00-05

Mincer · #2 (**permalink**) 12-08-03, 05:24

Your inserts should be:

Code:

UPDATE
    mail
SET
    mail_received_date = 'YYYY-MM-DD'
where
    mail_id = '1' ;

..and only if the mail_id column is a char/string type. If it's a number, it should be:

Code:

UPDATE
    mail
SET
    mail_received_date = 'YYYY-MM-DD'
where
    mail_id = 1 ;

(note, no quotes on mail_id)

Regards,

Matt.