SQL中如何按日期对字段排序？附sh_date字段日期排序示例

Hey there! The problem you're facing is that your sh_date field is stored as a text string in DD.MM.YYYY format. When you sort strings directly, SQL uses lexicographical (dictionary) order—which is why 01.01.2018 comes before 31.12.2017 (since "0" < "3"). To fix this, you need to convert the string to a proper date type first, then sort on that converted value.

Here are solutions for the most common SQL dialects:

MySQL/MariaDB #

Use the STR_TO_DATE() function to parse the string into a date, then sort by that date:

SELECT sh_date
FROM your_table_name
ORDER BY STR_TO_DATE(sh_date, '%d.%m.%Y');

• %d = two-digit day
• %m = two-digit month
• %Y = four-digit year

PostgreSQL #

Use TO_DATE() to convert the string to a date:

SELECT sh_date
FROM your_table_name
ORDER BY TO_DATE(sh_date, 'DD.MM.YYYY');

SQL Server #

Use CONVERT() with style code 104 (which corresponds to DD.MM.YYYY):

SELECT sh_date
FROM your_table_name
ORDER BY CONVERT(DATE, sh_date, 104);

If you want to handle invalid date strings gracefully (without throwing errors), use TRY_CONVERT() instead:

SELECT sh_date
FROM your_table_name
ORDER BY TRY_CONVERT(DATE, sh_date, 104);

Pro Tip #

If you can modify your table structure, change the sh_date column to a native DATE type instead of storing dates as strings. This will make sorting, filtering, and date calculations faster and more reliable in the long run. For example, in MySQL:

ALTER TABLE your_table_name MODIFY COLUMN sh_date DATE;

Just make sure all existing sh_date values are in a valid DD.MM.YYYY format before running this!

内容的提问来源于stack exchange，提问作者Smritimay Debnath