how to change date format into Y-m-d, i have stored in DB varchar

[ramsiva]

value is 150916124549 how to change the value to Y-m-d
any body help me

[cereal]

Hi, if that is a unix timestamp then do:

-- updating date format
UPDATE `table_name` SET `col_name` = FROM_UNIXTIME(`col_name`);

-- changing colum type to datetime
ALTER TABLE `table_name` MODIFY `col_name` datetime null;

If you want to preserve the old value, then add a new column and update against it:

ALTER TABLE `table_name` ADD `new_col_name` datetime null;
UPDATE `table_name` SET `new_col_name` = FROM_UNIXTIME(`col_name`);

More information here:

• https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

[almostbob]

echoing cereal
update the table to store the date as a date object, timestamp occupies 4 bytes/record important when you have 100 million records
date objects are designed for datetime use
select is simply numeric (orders of magnitude faster)
date object can be parsed to the text format required by the user on output, or from the text format required by the user on input, input fields for date have a standardised form
very few people, as a %age of the world, recognise any single text date form

if you just want the date from existing data

echo date("Y-m-d" , $date); 

where $date is the value returned from the db

[almostbob]

<?php date_default_timezone_set('America/Halifax'); 
echo '150916124549  = '.date("Y-m-d" , 150916124549); ?> 

150916124549 = 1988-10-07

I get bored easily