Auto pickup dates from SQL Server - T-SQL

Question

I am looking for some T-SQL code that should pick the date which is "One Year back from current date (at the same time last Sunday in the month of January)".

For example:

Current day      expected result
2017-02-05       2016-01-31
2017-01-05       2015-01-25
2018-02-19       2017-01-29
2018-01-19       2016-01-31
2019-02-28       2018-01-28

Please note: The year starts from last Sunday in January

I have some T-SQL code which is being used in SQL Server 2014:

select 
convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(GetDate()) = 1 THEN CONVERT(VARCHAR(4), GetDate(), 112) - 1 ELSE CONVERT(VARCHAR(4), GetDate(), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)

The above code picks the date for current year's (last Sunday in January month). But I want T-SQL code to pick last year's (last Sunday's date in January month) date.

In detail - I want T-SQL code to produce expected result from below table

Current day      T-SQL code answer       expected result
2017-02-05       2017-01-29              2016-01-31
2017-01-05       2016-01-31              2015-01-25
2018-02-19       2018-01-28              2017-01-29
2018-01-19       2017-01-29              2016-01-31
2019-02-28       2019-01-27              2018-01-28

Any help please.

Parsing and manipulating dates as strings is generally frowned upon and I gather that you did not write that expression. If you want a date from one year ago, just replace every `getdate()` with `dateadd(year, -1, getdate()`. — shawnt00, Feb 23 '17 at 05:16

score 0 · Answer 1 · edited May 23 '17 at 12:25

The best thing for this question is a numbers and date table. This answer shows you how to create one. Such a table is very handsome in many situations...

If I understand this correctly, you want the last Sunday in January of the previous year in all cases? Try this:

DECLARE @dummy TABLE(ID INT IDENTITY,YourDate DATE);
INSERT INTO @dummy VALUES
('2017-02-05'),('2017-01-05'),('2018-02-19'),('2018-01-19'),('2019-02-28');

WITH Years AS
(
    SELECT * FROM (VALUES(2010),(2011),(2012),(2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020)) AS t(Yr)
)
,LastSundays AS
(
    SELECT Yr AS TheYear
          ,DATEADD(DAY,(DATEPART(WEEKDAY,LastOfJanuary) % 7)*(-1),LastOfJanuary) AS LastSundayOfJanuary
    FROM Years
    CROSS APPLY(SELECT CAST(CAST(Yr AS VARCHAR(4)) + '0131' AS DATE)) AS t(LastOfJanuary)
)
SELECT * 
FROM @dummy AS d
INNER JOIN LastSundays AS ls ON YEAR(DATEADD(YEAR,-1,d.YourDate))=ls.TheYear;

The result (I do not understand row 2 and 4 completely...)

ID  YourDate    TheYear LastSundayOfJanuary
1   2017-02-05  2016    2016-01-31
2   2017-01-05  2016    2016-01-31 <--Your sample data is different...
3   2018-02-19  2017    2017-01-29
4   2018-01-19  2017    2017-01-29 <--Your sample data is different...
5   2019-02-28  2018    2018-01-28

Hint You might need to introduce @@DATEFIRST into your calculations...

Methinks your confusion is based on the OP defining a "year" as _starting_ on the last Sunday in January. As such, 2017-01-05 is in OP year 2016, not 2017. Similarly, 2018-01-19 is in OP year 2017, not 2018. In both cases the result year needs to be bumped back by one. The remaining dates are after the first Sunday in January and the Gregorian and OP years happen to match. — HABO, Feb 22 '17 at 04:33

score 0 · Answer 2 · answered Feb 21 '17 at 23:45

Here is a way to do it without a date table (which is still a good idea BTW). Tested on all your inputs and it delivers the correct output each time. Obviously you would refactor this a bit as it's longwinded, just to show each step.

/* The input date. */

DECLARE
   @input DATE = '2019-02-28';

/* The input date less one year. */

DECLARE
   @date_minus_one_year DATE = DATEADD(yy,-1,@input);

/* The year part of the input date less one year. */

DECLARE
   @year_date_part INT = DATEPART(yy,@date_minus_one_year);

/* 31 Jan of the previous year. */

DECLARE
   @prev_year_jan_eom DATE = CAST(CAST(@year_date_part AS VARCHAR(4))+'-01-31' AS DATE);

/* What day of the week is 31 Jan of the previous year? */

DECLARE
   @previous_eom_dw_part INT = DATEPART(dw,@prev_year_jan_eom);

/* Offest 31 Jan to the previous Sunday, won't change if the 31st is itself a Sunday. */

DECLARE
   @output DATE = DATEADD(dd,1 - @previous_eom_dw_part,@prev_year_jan_eom);

/* Input and output */

SELECT
   @input input
  ,@output [output];

shawnt00 · Answer 3 · 2017-02-23T23:35:23.983

I didn't think of a way to do it without the conditional in a case. It also uses the trick of casting a numeric year value to a January 1st date.

select case
  when
    datepart(dayofyear, dt) >
        31 - datepart(weekday, dateadd(day, 30, cast(year(dt) as varchar(4))))
  then
    dateadd(day,
        31 - datepart(weekday, dateadd(day, 30, cast(year(dt) as varchar(4)))),
        cast(year(dt) as varchar(4))
    )
  else
    dateadd(day,
        31 - datepart(weekday, dateadd(day, 30, cast(year(dt) - 1 as varchar(4)))),
        cast(year(dt) - 1 as varchar(4))
    )
end
from (values
    ('20100201'), ('20110301'), ('20120401'),
    ('20130501'), ('20140601'), ('20150701'),
    ('20160801'), ('20170901'), ('20181001')
) t(dt)

Just for fun (untested)

select
    dateadd(week,
        -52 * ceil(sign(datediff(day, dt, hs)) + 0.5),
        js
    )
from
    (select <date> dt) as t
    cross apply
    (
    select 31 - datepart(weekday,
         datefromparts(year(dt), 1, 31) as js
     ) t2;

score 0 · Accepted Answer · answered Mar 01 '17 at 08:30

SELECT
  convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)

Auto pickup dates from SQL Server - T-SQL

4 Answers4