0

I have a large query, where I group things by:

date_format(date, '%Y-%m')

and it works perfectly fine. I want later to turn it back to date to use datediff or whatever other date functions, then go back to this format. Of course, I tried STR_TO_DATE function:

str_to_date(date_format(date, '%Y-%m'),'%Y-%m')

but it returns NULL. According to documentation, it should return date in format 2020-01-00, but it doesn't. When I tried just:

select str_to_date('2020-01-01','%Y-%m-%d')

It works. I also tried:

SELECT STR_TO_DATE('9','%m')

directly from documentation, but it returns null too. I have MySQL 8.0.23.

I found some questions on it and tried all the things I found there: MySQL STR_TO_DATE() function returns null I also tried to collate it, but without success. Is there some workaround for it?

Manaslu
  • 156
  • 11
  • `STR_TO_DATE` needs in complete date, it cannot think of a day value. If you "want later to turn it back to date to use datediff or whatever other date functions" then add one more column into intermediate rowset, with complete date, and use it instead of partial column value later. – Akina Apr 23 '21 at 08:57
  • @Akina According to documentation in the link it should be possible without full date – Manaslu Apr 23 '21 at 09:04
  • This depends on SQL Mode. NO_ZERO_DATE and NO_ZERO_IN_DATE must be disabled at least (also complex strict mode must be disabled too). – Akina Apr 23 '21 at 09:11
  • good point, I didnt notice that. Is there a way to omit that without changing mode in general or creating another column? – Manaslu Apr 23 '21 at 09:16
  • 1
    Simply provide `-01` for the day part instead of `-00`. – IVO GELOV Apr 23 '21 at 10:06
  • @IVOGELOV perfect, works. thank you (though there was no -00 anyway :D). Here the code that worked: select str_to_date(date_format("2020-02-01", '%Y-%m-01'),'%Y-%m-%d') – Manaslu Apr 23 '21 at 10:25

0 Answers0