CODEWITHSUNDEEP
mysql
ScreamX
ScreamX

Reputation: 339

Does not count the week number correctly

In SQL query I use weekly grouping. But unfortunately, it displays incorrect data. in the new year, for some reason, the wrong week number is set. January 1 is 2021 - 52 week (2021-52), but SQL considers it as new - 2022 year 0 week (2022-00). Is it possible to fix this by some kind of checking or date conversion? (for example, as in php strtotime 'Monday this week')

select
    DATE_FORMAT(if(bso.STATUS_ID in ("N", "A", "R", "T", "Z"), if(isnull(bso.comments), "2222-01-01 00:00:01", "2222-01-02 00:00:01"), cast(bso.DATE_INSERT as DATE)), "%d.%m.%Y") as `DATE`,
    DATE_FORMAT(STR_TO_DATE(DATE_FORMAT(if(bso.STATUS_ID in ("N", "A", "R", "T", "Z"), if(isnull(bso.comments), "2222-01-01 00:00:01", "2222-01-02 00:00:01"), cast(bso.DATE_INSERT as DATE)), "%d.%m.%Y"), "%d.%m.%Y"), "%Y-%u") as `WEEK`
from `b_sale_basket` bsb
    left join b_sale_order bso on bso.ID = bsb.ORDER_ID
    left join b_iblock_element bie on bie.id = bsb.PRODUCT_ID
    left join b_sale_order_props_value as bsopvs on bsopvs.`ORDER_ID`=bso.`ID` and bsopvs.`CODE`="SOURCE"
where not isnull(bsb.ORDER_ID) 
    and bso.`DATE_INSERT` >= "2021-12-27 00:00:00" and bso.`DATE_INSERT` <= "2022-01-02 23:59:59"
    and bso.`CANCELED`!="Y"
    and bie.IBLOCK_ID = 1
group by DATE_FORMAT(STR_TO_DATE(DATE, "%d.%m.%Y"), "%Y-%u")

Output: enter image description here

P.S. 2222 date is correct, I need it to filter out, do not pay attention. I will thank you in advance for the hint

Upvotes: 0

Views: 57

Answers (1)

trincot
trincot

Reputation: 350270

For the date format you have used %u for the week number, which indeed allows 0. You can consider using %v, but then the downside is that the year will still remain 2022, as it is produced by a separate %Y specifier.

The solution is to first convert the date to the first day of the week it is in, and format that date.

Upvotes: 1

Related Questions