You can use ORDER BY
inside GROUP_CONCAT()
function. You can also change the separator, if you don't want to use comma. Further details in MySQL documentation: GROUP_CONCAT()
Example:
SELECT
product_id,
GROUP_CONCAT(name
ORDER BY language_id
SEPARATOR ','
) AS product_names
FROM
products_languages
GROUP BY
product_id ;
When a date is represented as 8 numerals without any delimiters, SQL Server will always try to interpret it as YYYYMMDD
. That format has deliberately been chosen as locale- or culture-independent (being also one of ISO 8601 standard date formats, as a matter of fact), and there is no way to have the server interpret it as DDMMYYYY
or in any other way.
So, either rearrange the date parts of your DDMMYYYY
string to make it YYYYMMDD
:
CONVERT(
date,
SUBSTRING(@datestring, 5, 4) + SUBSTRING(@datestring, 3, 2) + SUBSTRING(@datestring, 1, 2)
)
or insert delimiters to make it match one of the known delimited DMY formats, for instance DD/MM/YYYY
:
CONVERT(
date,
STUFF(STUFF(@datestring, 5, 0, '/'), 3, 0, '/'),
103 -- DD/MM/YYYY
)
Best Answer
You are looking for the
STR_TO_DATE()
function. MySQL expects by default dates in ISO format.In your particular case it would be something like:
%d-%m-%Y
or%m-%d-%Y
, whatever is the "wrong" format you are using.Using ISO dates (either strings or, preferably, timestamps) plus an index will have the efect that it will speed up your queries.