Uk date format in select query

[ianhaney]

Hi

 

Sorry quick one, I am trying to make the date format into UK format and have entered the following into the SELECT query but is returning 0 results where as it should have two results

 

I have the following SQL query

$sql=$dbh->prepare("SELECT 
      e.emp_id
    , t.task_id
	, t.customer_name
	, t.customer_phone
	, t.customer_address
	, DATE FORMAT(t.date_of_repair,'%d/%m/%Y') AS date_of_repair
    , t.description 
    , t.status
    FROM employee e
    JOIN assignment a ON e.emp_id = a.emp_id
    JOIN task t ON a.task_id = t.task_id
    WHERE e.emp_id = ?");
$sql->execute(array($_SESSION['user']));

[ianhaney]

Sorry I did it another way by doing the following

 

I added in this below

date_default_timezone_set('Europe/London');

Then I did the following

echo "<td>" . date('d/m/Y', strtotime($row->date_of_repair)) . "</td>";

Works perfect this way

[Barand]

DATE FORMAT should not be two words, it's DATEunderscoreFORMAT

 

eg

 

SELECT DATE_FORMAT(t.date_of_repair, '%d/%m/%Y')

[ianhaney]

Ahh ok what way is better or does it not matter as much if done the way I have done it or the DATE_FORMAT way?

[Barand]

No hard and fast rule. If all you want to do with date is display it then it doesn't really matter, though it's easier to to do it in the query.

 

If you need to do some further processing of the date in your PHP then select the raw date (yyyy-mm-dd) in the query, as d/m/y it useless for anything other than display.

[ianhaney]

Ok

 

Thank you for the advice and replies, appreciate it