Question 19987 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL to default the end date based on previous week's last date

How do I go about making the in_date’s end date always based on the previous week’s last date?
For example today is 24 April 2023, and the previous week’s (Mon to Sun) last date was 16/04/2023

Expected SQL Query:

SELECT ga.in_date,
        FROM in_txn ga
        LEFT JOIN cntr_mst cm ON cm.cntr_num = ga.cntr_num
        WHERE status_active = 'A'
        and (in_date between '2020-01-01 00:00:00' and '2023-4-16 23:59:59')
Tags:

2

Answers


  1. 0

    I suggest this:

    SELECT
    ga.in_date,
    DATE_SUB(DATE_ADD(DATE(ga.in_date), INTERVAL(1-DAYOFWEEK(ga.in_date)) DAY), INTERVAL 1 DAY) AS the_new_date
FROM in_txn ga
LEFT JOIN cntr_mst cm ON cm.cntr_num = ga.cntr_num
WHERE status_active = 'A' 
AND in_date >= '2020-01-01 00:00:00' AND in_date < '2023-4-17 00:00:00';

    Also: between is painful! as "23:59:59" might not be the last moment of the day (the maximum precision of a MySQL TIMESTAMP column is up to microseconds , 6 digits) so it is so much easier – and more reliable – to avoid using it. Note that I have moved the second date up one second.

    see: https://dbfiddle.uk/DL-cJL8n

    Login or Signup to reply.
  2. 0

    You want to filter from the beginning of year 2020 to the end of last week.

    We can compute the date of the last Monday (or today if it’s a Monday) with current_date and weekday():

    current_date - interval weekday(current_date) day

    And we can use this expression to filter with a half-open interval, voilà!

    WHERE status_active = 'A' 
  AND in_date >= '2020-01-01'
  AND in_date <  current_date - interval weekday(current_date) day
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search