0

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 ?

3
Contributors
7
Replies
8
Views
5 Years
Discussion Span
Last Post by simplypixie
0

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']));
}
0

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'

0

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";
0

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.

0

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.

0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.