how to store US DATE formate( like 27-02-2007) in mysql table

[raahat]

hi,
i need to do desperately the way of storing american DATE formate( like 27-02-2007) in mysql table.i searched on the web a lot,but didnt find anything.PLZ i need someone's help.

[vpmurdan]

Hi.
I think MySQL always stores date as an integer number. you can format the output when you select a field, e.g.

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
        -> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
                          '%D %y %a %d %m %b %j');
        -> '4th 97 Sat 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
                          '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'

The format codes is given in the MySQL documentation, search for 'DATE_FORMAT'

ELSE

you can store your date field in a varchar field. But you will have to maintain the format. and if you want to retrieve the field as a date you have to convert the field .


Prakash

[raahat]

thnx for ur reply.........i already knew abt SELECTing in american format from a table, and i knew abt storing date in VARCHAR as well........... ..but i need to a create a date column in a table with date type and american format(27-02-2007) only.........co uld u help plz.

[ronverdonk]

You cannot make a DATE data type field that is going to contain a European data format (dd-mm-yyyy). The US format of a date is mm-dd-yyyy.

And why would you care? You store a date as a date in MySQL date format and when you display it you can transform it to any format you like using the DATE_FORMAT() funrcion.

Ronald :cool: