asked on

MySQL - sort records by date field with format DD/MM/YYYY HH:MM ?

Hi,
If I have a table as below, how would I sort records by `date_created` column where date input is in format DD/MM/YYYY HH:MM ?

Thanks in advance for your feedback.

CREATE TABLE IF NOT EXISTS `blogs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_created` varchar(30) DEFAULT NULL,
`blogName` varchar(50) DEFAULT NULL,
`allowComments` tinyint(4) DEFAULT '1',
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=154 ;

--
-- Dumping data for table `blogs`
--

INSERT INTO `blogs` (`id`, `date_created`, `blogName`, `allowComments`) VALUES
(134, '18/02/2012 09:00 ', 'Registration Day', 0),
(135, '18/03/2012 22:35 ', 'Level 1 Coaches Course',0),
(140, '12/04/2012 19:00 ', 'Sample Heading 2', 1),
(141, '13/04/2012 18:22 ', 'Dates for Term Two',0),
(142, '20/04/2012 11:00 ', 'Sample Heading 2', 0),
(143, '13/06/2012 18:22 ', 'Sample Heading 3',0),
(144, '20/04/2012 11:00 ', 'Sample Heading 4', 0),
(145, '11/03/2012 22:36 ', 'Presentation Day', 0);

Anuroopsundd

ORDER by `date_created` DESC

ASKER CERTIFIED SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tangchunfeng

mySQL convert varchar to date

alter table blog add (realdate timestamp );
update table set realdate = str_to_date(date_created, '%M/%d/%Y %h:%i');

sabecs

ASKER

Thanks for your help and comments, the date time format is coming from a jQuery calendar so is it possible to sort it woithout chnaging the format in the table?

johanntagle

Thanks for your help and comments, the date time format is coming from a jQuery calendar so is it possible to sort it woithout chnaging the format in the table?

You already have your answer to this above. "order by str_to_date(date_created, '%d/%m/%Y %H:%i')" will order the results, but not the format of the results. However, we are telling you this is not the ideal way. If jQuery gives the date in such a format then use str_to_date to store it as datetime when you write it to the table. Then when you need to show it in a specific format then use date_format. It seems more work but it is better this way as you'll get better performance and can do more calculations on the data later on, since it is stored using the native datetime datatype.

sabecs

ASKER

Thanks for your help, much appreciated..