I am trying to combine a MySQL query with change date format so all rows display as d-m-Y (surely possible ?) in results. The datefield type is DATE.
$query="SELECT * FROM table DATE_FORMAT('datefield','%d-%m-%Y')AS datefield FROM table";SELECT*FROM table on its own displays datefield as YYYY-MM-DD but
DATE_FORMAT does not change anything. All I get is a syntax error from ('datefield on....
Am I anywhere near ?
You could do it in PHP instead (also you have to FROM table in your query which is wrong)
$query=mysql_query("SELECT * FROM table");
$result=mysql_fetch_array($query);
while ($row = $result=mysql_fetch_array($query)) {
$datefield = date('d-m-Y', strtotime($row['datefield']));
}Thanks for reply.
I had tried something similar before but find that I end up with 01-01-1970 where I have '0000-00-00' in the datefield.
Some of the dates have not yet been input therefore I wish to retain them as 'nil'
SINGLE QUOTE IS INVALID AROUND DATEFIELD, its a column name not a text, so remove single quote surronding 'datefield'
$query="SELECT * FROM table DATE_FORMAT(datefield,'%d-%m-%Y')AS datefield FROM table";I had tried something similar before but find that I end up with 01-01-1970 where I have '0000-00-00' in the datefield.
Some of the dates have not yet been input therefore I wish to retain them as 'nil'
With what I put, you will need to resolve this by using an if statement to ignore dates that are 0000-00-00.
I had tried something similar before but find that I end up with 01-01-1970 where I have '0000-00-00' in the datefield.
Some of the dates have not yet been input therefore I wish to retain them as 'nil'
With what I put, you will need to resolve this by using an if statement to ignore dates that are 0000-00-00.
Thanks simplypixie.
I gave up on the DATE_FORMAT query (only ever got syntax error)
The PHP route worked just fine. I managed to write an IF statement, now I display dd-mm-yyyy and 0 where needed.
Not a problem - glad to hear it is working for you now:)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.