Date_format

[furlanut]

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 ?

[simplypixie]

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

[furlanut]

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'

[urtrivedi]

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

[simplypixie]

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.

[simplypixie]

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.

[furlanut]

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.

[simplypixie]

Not a problem - glad to hear it is working for you now:)