-1

I have a string of dates in php (dd/mm/yyyy) that I want to convert to yyyy-mm-dd format.

I have tried :

UPDATE `table` SET `DATE_VAR` = convert(datetime,'19/03/2015', 121) WHERE `ID`=160

Can you help me?

shadow
  • 21,823
  • 4
  • 63
  • 77
Neyoh
  • 623
  • 1
  • 11
  • 33
  • Do not store a date as a text in your db. use a date Format. Also this Looks like mssql query not mysql. – Jens Mar 16 '15 at 17:12
  • Also look at STR_TO_DATE, and the manual more generally. – Strawberry Mar 16 '15 at 17:19
  • it's good with Ronak Patel solution, with PHP it's easy ! – Neyoh Mar 16 '15 at 17:22
  • You mix MSSQL and MySQL. Which one do you use ? – Lorenz Meyer Mar 16 '15 at 17:31
  • You really, really, **really** should be storing your dates as a `DATE` column and leaving the formatting up to your application. Dates stored as strings are nearly useless since they can't be depended on, you have zero date validation and you can't compute differences. – tadman Mar 16 '15 at 17:35

2 Answers2

1

If you want to do the date conversion in PHP. Then one of the solutions could be:

$originalDate = str_replace('/', '-', "19/03/2015");
$newDate = date("Y-m-d", strtotime($originalDate)); // This is your mysql compatible date...

You can use $newDate variable in query.

UPDATE `table` SET `DATE_VAR` = '$newDate' WHERE `ID`=160
RNK
  • 5,582
  • 11
  • 65
  • 133
0

object oriented style:

echo DateTime::createFromFormat('d/m/Y', $date)->format('Y-m-d');

where $date is the date in dd/mm/yyyy format.

itachi
  • 6,323
  • 3
  • 30
  • 40