I have dates in varchar type like:

• 201601
• 201602
• 201603
• 201701
• 201702 and so on

I am trying to view all my records where the dates are in ascending order. So I am using this query:

SELECT * FROM emp_pp GROUP BY YEARMM ORDER BY STR_TO_DATE(YEARMM,'%Y%m')

Here YEARMM is my column name. The query isn't working properly when I run it. Instead I keep getting all these notices:

Incorrect datetime value: '201601' for function str_to_date
Incorrect datetime value: '201602' for function str_to_date...

Why is that? Please help me

CodePudding user response：

As mentioned by Akina, STR_TO_DATE function requires enough data to generate at minimum a full date value to work correctly. You do not have that (you can not have a date 2016-02-00, for example).

What you do have is a numerical Year and Month integer, 201601, 201602, 201603, 201701 etc. which orders exactly as you want by standard numerical ordering so all you need to do is remove the STR_TO_DATE part entirely:

SELECT * FROM emp_pp GROUP BY YEARMM ORDER BY YEARMM ASC /* Oldest date first */

Of note:

• SELECT * is highly inefficient. You should name each column you want to collect.
• Column names in SQL should not be upper case, as this is hard to read with the correct case syntax, SQL column should be only lower case; yearmm would be more readable in your SQL code.