Reformat DATETIME type value to European date format

Question

I have a select query which returns a DATETIME value (stored format: YYYY-MM-DD HH:MM:SS).

I want to show this on my site in "Euro date format" in other words d-m-y format.

So what sql code should I use in my SELECT query to show d-m-y format rather its current YYYY-MM-DD HH:MM:SS format?

I would like the value to be in the following date-only format: DD-MM-YYYY

Answer 1

The joomla use in php code articles this code to show date and time:

<?php
echo JHtml::_(
'date', $article->displayDate,
$this->escape($this->params->get('date_format', JText::_('DATE_FORMAT_LC3')))
); ?>

Joomla use string 'DATE_FORMAT_LC3' to format date and time. If your site was use language english you can change the file in: /language/en-GB/en-GB.ini in lines around 261-266

DATE_FORMAT_LC3="d F Y"

change to

DATE_FORMAT_LC3="dd-mm-yyyy"

Answer 2

Rather than trying to format using mysql, i would rather to do it in php directly using JDate.

for example:

// Your date from database
$myStringDate = '2018-06-19 09:04:05';

// Use it in a JDate instance
$date = JFactory::getDate($myStringDate);

// Output using the format of your choice (see http://php.net/manual/en/function.date.php)
echo $date->format('d F Y');

Answer 3

DATE_FORMAT() is a highly versatile and concise tool to reformat your datetime stamp within the query (before it hits the php layer).

The advantage of using a pure MySQL solution is that you are faaar less likely to need to adjust this syntax versus needing to adjust a Joomla syntax because of a version change.

Using pure MySQL also results in less overhead/handling, so it will peeform more efficiently.

Here are some pertinent format specifiers:

%m -> Month, numeric (00..12)
%ac -> Month, numeric (0..12)
%d -> Day of the month, numeric (00..31)
%e -> Day of the month, numeric (0..31)
%Y -> Year, numeric, four digits
%y Year, numeric (two digits)

I believe you are wanting zero-padded days and months and a four-digit year, so my recommended SELECT query looks like this:

SELECT DATE_FORMAT(`timestamp`, '%d-%m-%Y') AS `Eurodate`

DBFiddle Demo