How to get previous year from day of week and week

user_14273161 0
Viewed 113

I have function get day of week and week from current day in mysql It look like.

select 
   DATE_FORMAT(now(), '%U') as w,
   DATE_FORMAT(now(), '%w') as day_of_w;

It return w is 37 and day_of_week is 1. How to get correct value w is 37 and day_of_week is 1 of previous year.

I using

select 
   DATE_FORMAT(now() interval 1 year, '%U') as w,
   DATE_FORMAT(now() interval 1 year, '%w') as day_of_w;

But SQL cannot execute.

mysql sql
3 Answers

The function to turn a string into a date is STR_TO_DATE. If you want to get the date for week 37, day 1 in 2019, you could use

select str_to_date('37 1 2019', '%U %w %Y')

If you want the date for today's week and day number in last year:

select str_to_date(concat_ws(' ', date_format(current_date, '%U'),
                                  date_format(current_date, '%w'), 
                                  year(current_date) - 1),
                   '%U %w %Y')

Be aware though, that some years have a week 53 while others don't. If you run this query in a year's 53rd week, you don't get a valid result.

user_22707620 
select DAYOFWEEK(now()) day_of_week, week(now()) week from dual

your query has syntactical problem. you forgot +/- symbol

select DATE_FORMAT(now() + interval 1 year, '%U') as w
      ,DATE_FORMAT(now() + interval 1 year, '%w') as day_of_w;
user_11189780

You just need to use DATE_ADD function in MYSQL to change the date to last year.

select DATE_FORMAT(DATE_ADD(now(), INTERVAL -1 YEAR), '%U') as w
,DATE_FORMAT(DATE_ADD(now(), INTERVAL -1 YEAR), '%w') as day_of_w;

Result:

36  6

Current and last year date:

select now() as curr_yr_date, DATE_ADD(now(), INTERVAL -1 YEAR) as last_yr_date;

Result:

2020-09-14 07:13:24 2019-09-14 07:13:24
user_76763810
Related