Uffo Uffo - 4 months ago 17
SQL Question

SQL query debug

Can someone explain me this mysql query:

SELECT E.*,
LAST_DAY(
STR_TO_DATE(
concat(
LPAD(
month(
STR_TO_DATE(SUBSTRING(C.month, 1, 3),'%b')), 2, 0
), '/', '01', '/', C.year),'%m/%d/%Y')) as calendar_date


I don't understand what date it selects.... one week, or?

Answer

Based on the str_to_date's use of %b and reading the following: MySQL: STR_TO_DATE Function,

the data would have to have date info like JAN FEB MAR which explains the substring of 3 usage. As said in comments, it returns the last date of the month such as

2006-01-31
2016-09-30

MySQL Manual Page for str_to_date() and LPAD()

As for LPAD, that one left pads the month with leading left-most 0's. In that case just one, so a 7 would go to 07, for instance, for July. A Date of 2016-7-31 would not be happy later. But a Date of 2016-07-31 would be.