SQL query to find dates of last year equivalent to today's date

0
votes

I am writing a SQL query to find business working dates of last year equivalent to today's date.

In this query it should fetch :-

For e.g. if today is 5th January, 2021 and it is the second day of second week of the year. So I need to find the exact equivalent date of the second day of second week of the previous year. So it would be 7th January, 2020.

And with this, I need the business working dates of that week of 7th January 2020 (i.e. excluding Saturday & Sunday) Which will come up as 2020-Jan-06 to 2020-Jan-10 according to the example.

So I will need the report between 6th Jan - 10th Jan, 2020.

I am trying to use this code to find date of last year equivalent to today's date (5th Jan, 2021 viz. second day of second week)

select Convert(date, (DATEADD(year, -1, getdate()+2))) ;

2
sqlsql-server
Please explain how 2021-01-05 is "the second day of second week of the year". – Gordon Linoff
Hi @GordonLinoff, according to the business scenario, the week will start from Monday and so 5th Jan, 2021 will be second day of the second week of the year. – Amar Kumar
Your previous question has suggestions for a calendar - use one of those. You just add whatever columns you need to know that Jan 7 2021 correlates to Jan 5 2020 – SMor

2 Answers

1
votes

2021-01-05 is the 2nd day of the first week of 2021 according to ISO standards.

If you want the 2nd day of the first week of 2021, then it is either today's date minus 52 weeks or 53 weeks. Based on the Wikipedia page for ISO dates:

[53 week years are those] years in which 1 January or 31 December are Thursdays

So, we want that for the previous year. Hence, I think the following should work:

select dateadd(week,
               (case when 'Thursday' in (datename(weekday, datefromparts(year(getdate()) - 1, 1, 1)),
                                         datename(weekday, datefromparts(year(getdate()) - 1, 12, 31))
                                       )
                    then -53 else -52
               end),
               convert(date, getdate()) 
              )

Note that this returns 2019-12-31, which is the correct value based on ISO standards.

0
votes

I have use multiple CTE to show you the step by step calculation. It should be pretty easy to follow.

Basically it find the week_no and day_no_of_week for 2021-01-05 and then use that to find the same date for 2020

declare @input_date     date = '2021-01-05',
        @year_offset    int  = -1;  -- previous year

with 
cte1 as
(
    select  input_date          = @input_date,
            week_no             = DATEPART(WEEK, @input_date),
            first_day_of_week   = DATEADD(WEEK, DATEDIFF(WEEK, 0, @input_date), 0)
),
cte2 as
(
    select  *,
            day_no_of_week = DATEDIFF(DAY, first_day_of_week, @input_date) + 1
    from    cte1 
),
cte3 as
(
    select  *,
            first_day_of_the_prev_year = DATEADD(YEAR, DATEDIFF(YEAR, 0, @input_date) + @year_offset, 0)
    from    cte2
),
cte4 as
(
    select  *,
            first_day_of_week_prev_year = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(WEEK, week_no - 1, first_day_of_the_prev_year)), 0)
    from    cte3
)
select  *,
        DATEADD(DAY, day_no_of_week - 1, first_day_of_week_prev_year) as the_required_date
from    cte4