Mysql get date interval from year and weekNumber

user_9334111 0
Viewed 54

I have a table that has fields year, week_number, stock_count. How will I get all rows that fall within a given date range given start_date and end_date?

sequelize.js datetime mysql
2 Answers

The example below assumes that the start_date is 2020-02-28 and end_date is 2021-01-31:

SELECT *
FROM `your_table_name`
WHERE (
        `year` > YEAR('2020-02-28') OR 
        (`year` = YEAR('2020-02-28') AND `week_number` >= WEEK('2020-02-28'))
    ) AND (
        `year` < YEAR('2021-01-31') OR 
        (`year` = YEAR('2021-01-31') AND `week_number` <= WEEK('2021-01-31'))
    )
user_63944040

If your table does not have date field, you can not use start/end_date. => this would be the "normal" option.

In your case, (I presume this is what you want), you can only extract the year and week from the start/end_date and only then run a query.

SELECT WEEK('2020-05-18'); will return the week corresponding to that date

SELECT YEAR('2020-08-28'); will extract the year from your date

Then you can run your query based on the year and week.

I hope it makes sense...

user_180619010
Related