date data

hcd

I imported date data thru SQL, some how date (varchar) is turned 2010-11-20 (YYYY-MM-DD), I want to turn this as 11/20/2010 (MM/DD/YYYY). Can some one please help me how to turn this around. PHP/MYSQL. Thank you for your help advance.

NogDog

I feel I must start by asking: why do you want to store a date as a varchar type? Wouldn't it make more sense to store it as either a date or timestamp type?

hcd

I realised after importing as varchar.

Weedpacket

Couldn't you fix the data type of the column? Something like

ALTER TABLE tablename ALTER COLUMN columnname TYPE date USING to_date(datestring, 'YYYY-MM-DD')

or however that would be written in MySQL?

Then you'd be able to use the other date handling functions that MySQL provides for handling dates, including your desired goal of converting international standard format into US-specific format.

barand

If they're stores as yyyy-mm-dd then just alter the type from VARCHAR to DATE.

So long as you correctly store in yyyy-mm-dd then even as varchar they can be reformatted using DATE_FORMAT() eg

CREATE TABLE `test_date` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

mysql> SELECT date
    ->  , DATE_FORMAT(date, '%m/%d/%Y') as usdate
    -> FROM test_date;
+------------+------------+
| date       | usdate     |
+------------+------------+
| 2014-11-15 | 11/15/2014 |
| 2014-11-16 | 11/16/2014 |
| 2014-11-17 | 11/17/2014 |
| 2014-11-18 | 11/18/2014 |
| 2014-11-19 | 11/19/2014 |
| 2014-11-20 | 11/20/2014 |
+------------+------------+

sneakyimp

The point of changing the field type definition from varchar to date is that date fields can be automatically transformed into a variety of formats using such functions as DATE_FORMAT as pointed out by Barand. VARCHAR fields have no such magic transformation.