Search code examples
sqlsql-serverreporting-services

Getting the last day of the previous year in SQL

How can I get the last date of the previous year e.g. 20211231 in SQL?

Or even to get the previous last dates in a list

20211231
20221231
20191231
20181231

I want to use that info in SSRS.

Thank you.

Solution

  • DATEFROMPARTS would be one method:

    SELECT DATEFROMPARTS(YEAR(GETDATE())-1,12,31);

    Another, on old completely unsupported versions, would be to use some date math:

    SELECT DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0));

    In future versions you could also use DATE_BUCKET:

    SELECT DATEADD(DAY, -1, DATE_BUCKET(YEAR,1,GETDATE()));
--or use a different origin
SELECT DATE_BUCKET(YEAR,1,GETDATE(),CONVERT(datetime,'19001231'));

    Honestly, DATEFROMPARTS is probably the "cleanest" and easiest to understand.