Member Avatar for 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 ?

  • 3 Contributors
  • 7 Replies
  • 130 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by simplypixie

Dani AI

Generated

Short summary: the original syntax error came from a malformed SELECT and quoting the column name (as observed). ’s PHP approach is fine for display, but it needs a check for MySQL zero dates to avoid the 1970-01-01 result that happens when strtotime sees 0000-00-00. Below are safe, practical alternatives you can apply now.

A concise MySQL-only display that leaves the stored value intact and shows nil for zero dates:

SELECT
  COALESCE(
    DATE_FORMAT(NULLIF(datefield, '0000-00-00'), '%d-%m-%Y'),
    'nil'
  ) AS date_display
FROM your_table
ORDER BY datefield;

Explanation: NULLIF converts the zero-date string to NULL, DATE_FORMAT formats real dates, and COALESCE returns 'nil' when the result is NULL. Keep the formatted value under a different alias (here date_display) so you can still sort or filter by the original datefield.

A robust PHP display pattern (avoids the 1970 fallback and handles NULL/zero):

if (empty($row['datefield']) || $row['datefield'] === '0000-00-00') {
    $display = 'nil';
} else {
    $dt = new DateTime($row['datefield']);
    $display = $dt->format('d-m-Y');
}

If you control the schema, consider converting zero dates to real NULLs and allowing NULLs at the column level; that makes both SQL and PHP handling cleaner. Always back up before mass updates. These options give readable output while preserving correct sorting and query behavior for later use.

Recommended Answers

All 7 Replies

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

Member Avatar for 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";
Member Avatar for 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.

Member Avatar for 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.

Member Avatar for 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.

Member Avatar for simplypixie

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.