0

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

  • it appears that you have abandoned this page. Please accept the solution that you feel is best and upvote answers that you found helpful. If nothing helped and you still require assistance, please edit your question to clarify where you are stuck and leave comments for the volunteers who have tried to help you so far. – mickmackusa Jan 28 at 10:04
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"
1

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');
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.