-1

I have inserte date into my table and it is in the format YYYY-MM-DD, However when displaying this information I would like to display the date the in DD-MM-YYY format. If anyone has an idea how to achieve this please let me know. Please see my sql query below as this query it not working.

  // Create query using SQL string
  $sql_query = "SELECT title, level, DATE_FORMAT(dateTo, '%W %M %e 
   %Y') 
     FROM jobPost ORDER BY jobID DESC";

  // Query database using connection
 $result = $conn->query($sql_query);
Adam Mc Menamin
  • 27
  • 4
  • Please add expected output. Take current date as an example. – Madhur Bhaiya Nov 13 '18 at 13:52
  • What is wrong with your query? – Salman A Nov 13 '18 at 13:53
  • Don't do formatting in your data layer. In the database a date is a date is a date, it doesn't have a format until you turn it in to a string. Allow your SQL to return an actual date data type to your client, be that PHP or Python or a reporting tool, then use the client's native abilities to format the data however you like. Don't pass around dates as strings just so you can pre-format them. Next you'll want to turn numbers in to strings so that you can add leading 0's. – MatBailie Nov 13 '18 at 13:56

1 Answers1

0

You can use the following:

DATE_FORMAT(dateTo, '%d-%m-%Y')
/* For dateTo value of 2018-11-13, it will output 13-11-2018 */

Details:

  • %d Day of the month as a numeric value (01 to 31)
  • %m Month name as a numeric value (00 to 12)
  • %Y Year as a numeric, 4-digit value

Check the complete list of available format specifiers at: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57