changing the date_format

[sang]

Hai

I want to change the date format at the time of inserting the record.
i done it but one error in the insertion.

The query is

insert into mytable(update) values(date_format( '2006-10-09','%d%m%y');
Query OK
but in the out put it will be in the form of '2009-10-06' ie in the date field 20 is added

please help any one

Thanks in advance
Sang

[ronverdonk]

If the column containing the date has the MySQL DATE data type, it will store it as YYYY-MM-DD. See the following from the MySQL documentation at Overview of Date and Time types

DATE

A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using either strings or numbers.

In your case MySQL accepts '06-10-09' as year 2006, month 10 and day 9.
You can either use the standard date type and use date_format in the display of the date, or make the field a CHAR field but then it will be difficult to do arithmetic on its value. I would choose the first solution.

Ronald :cool:

[sang]

Thank you very much for your reply

now i am changing my format as string.

i am insert date like this '20-oct-2006' using data type varchar.

at the time of select i want to change the date format like this '20-10-2006'

i will try with this qurey but not working

that is

select name,curdate,date_format(curdate ,'%d%m%y') from mytable;
+-------+-------------+-------------------------------+
| name | curdate | date_format(curdate,'%d%m%y') |
+-------+-------------+-------------------------------+
| mysql | 20-oct-2006 | |
+-------+-------------+-------------------------------+
out put is like this

please give me the solution
Thanks
Sang

[ronverdonk]

I assume you have to use PHP to do that. A much better solution would be if you inserted your data as (I am jusing NOW for the date here) e.g.

INSERT INTO table_name (name, cur_date) VALUES('John', NOW())

and select back with

SELECT name, DATE_FORMAT(cur_date, '%d-%m-%y') as cur_date FROM table_name

That nwill give you the required 'day-month-year' format.
One of the main reasons to still use the DATE type for cur_date is that you can, when the need arises, do arithmetic on the dates.

Ronald :cool: