T-SQL autopickup date

Question

I am looking for some T-SQL code that should pick the date which is the "last Sunday in the month of January".

For example: Let's assume,

Senario 1: Current date is - '2017-01-29'

Code should pick - '2016-01-31'

Senario 2: Current date is - '2017-02-05'

Code should pick - '2017-01-29'

Senario 3: Current date is - '2017-02-19'

Code should pick - '2017-01-29'

Senario 4: Current date is - '2018-01-28'

Code should pick - '2017-01-29'

Senario 5: Current date is - '2018-02-04'

Code should pick - '2018-01-28'

Please Note: This is because Year starts from Last Sunday in January month

I have below some T-SQL code which is being used in SQL Server 2014. It always picks the 31st of January each year - which is wrong.

select CASE 
    WHEN    GETDATE() <= DATEADD(Day,-0,CONVERT(datetime, CONVERT(varchar(4), (year(GETDATE()))) + '-02-01'))
    THEN    DATEADD(Day,-1,CONVERT(datetime, CONVERT(varchar(4), (year(GETDATE())-1)) + '-02-01'))
    ELSE    DATEADD(Day,-1,CONVERT(datetime, CONVERT(varchar(4), (year(GETDATE()))) + '-02-01'))
END

Why is it always selecting January 31st?

score 0 · Answer 1 · answered Feb 21 '17 at 12:22

0

You can use the following SQL Query to get the last date from the given date

DECLARE @dtDate DATETIME
SET @dtDate = '2/21/2017'

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

Output: 2017-02-28 23:59:59.000

SELECT FORMAT(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0)), 'yyyy-MM-dd') AS LastDate

Output: 2017-02-28

answered Feb 21 '17 at 12:22

B.Balamanigandan

4,713
11
68
130

The desired output is last Sunday in January month, not the last Sunday of current date. – ahmed abdelqader Feb 21 '17 at 12:54

score 0 · Answer 2 · edited May 23 '17 at 12:09

The following T-SQL gives you the result you need.

Declare @dtMyDate datetime

select @dtMyDate = '2017-01-25'

select case 
        when datepart(month,@dtMyDate) = 1

        then convert(varchar(10), DATEADD(day,DATEDIFF(day,'19000107',
        DATEADD(month,DATEDIFF(MONTH,0, 
        CONVERT(date, CONVERT(VARCHAR(4), dateadd(year,-1,@dtMyDate), 112)+'0101')),30))/7*7,'19000107'), 120)

        else convert(varchar(10), DATEADD(day,DATEDIFF(day,'19000107',
        DATEADD(month,DATEDIFF(MONTH,0, 
        CONVERT(date, CONVERT(VARCHAR(4), @dtMyDate, 112)+'0101')),30))/7*7,'19000107'), 120)
        end

Result:-

2016-01-31

The references that helps me:-

how to extract current year and combine with specific month and date as a date

Find Last Sunday

thanks (if dates are between February to December it's giving correct answer). But if we set current date as '2017-01-25' (or any other dates in January Month) then the result is wrong. For the above date (2017-01-25) answer should be '2016-01-31' — user2331670, Feb 21 '17 at 16:44

score 0 · Answer 3 · answered Feb 21 '17 at 13:28

DECLARE @LastJanuarySunday DATE = CONVERT(DATE,CAST((SELECT DATEPART(YEAR, GETDATE())) AS VARCHAR(4)) + '-01-01')
DECLARE @LastDay DATE = 
(SELECT DATEADD(dd,-(DATEPART(WEEKday, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,convert(datetime,@LastJanuarySunday,112))+1,0)))-1),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,convert(datetime,@LastJanuarySunday,112))+1,0))))
DECLARE @InputDate DATE = GETDATE()


SELECT CASE WHEN @InputDate > @LastDay AND @LastJanuarySunday <> @LastDay
   THEN @LastDay
   WHEN @InputDate = @LastDay
   THEN DATEADD(DAY, 2, @LastDay)
   END

This will be useful to you.

thanks it works ok (if dates ate between February to December). But if we set current date as '2017-01-25' (or any other dates in January Month) then the result is null. Any help please? — user2331670, Feb 21 '17 at 16:25

Wes H · Answer 4 · 2017-02-21T18:30:18.590

The rightmost column of this will calculate the previous last Sunday in January for any date. I've left the other columns in as a simple way to document and experiment with the logic.

There is no branching logic, it is all pure date math so it should aggregate reasonably fast and should be able to use indexes on a date column.

DECLARE @Date DATE = '20170205';
SELECT SampleDate= @Date  ,
    EndOfSampleMonth = EOMONTH(@Date) ,
    SampleMonthNumber = DATEPART(MONTH,@Date) ,
    MostRecentJanuaryForSampleMonth = DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date) ,
    LastDayOfMostRecentJanuary = EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date)) ,
    WeekDayOfLastDayOfMostRecentJanuary = DATEPART(WEEKDAY,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date))) ,
    LastSundayOfJanuary = DATEADD(DAY,(DATEPART(WEEKDAY,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date)))-1)*-1,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date))) ,
    LastSundayOfJanuaryDayOfYear= DATEPART(DAYOFYEAR,DATEADD(DAY,(DATEPART(WEEKDAY,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date)))-1)*-1,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date)))) ,
    PushSampleDateToFiscalYear = DATEADD(DAY,(DATEPART(DAYOFYEAR,DATEADD(DAY,(DATEPART(WEEKDAY,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date)))-1)*-1,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date))))) * -1 ,@date) ,
    PreviousLastSundayInJanuaryForSampleDate = DATEADD(DAY,(DATEPART(WEEKDAY,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH
,DATEADD(DAY,((DATEPART(DAYOFYEAR,DATEADD(DAY,(DATEPART(WEEKDAY,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date)))-1)*-1,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date)))))-1) * -1 ,@Date))-1)*-1
,DATEADD(DAY,((DATEPART(DAYOFYEAR,DATEADD(DAY,(DATEPART(WEEKDAY,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date)))-1)*-1,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date)))))-1) * -1 ,@Date))))-1)*-1,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH
,DATEADD(DAY,((DATEPART(DAYOFYEAR,DATEADD(DAY,(DATEPART(WEEKDAY,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date)))-1)*-1,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date)))))-1) * -1 ,@Date))-1)*-1
,DATEADD(DAY,((DATEPART(DAYOFYEAR,DATEADD(DAY,(DATEPART(WEEKDAY,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date)))-1)*-1,EOMONTH(DATEADD(MONTH,(DATEPART(MONTH,@Date)-1)*-1,@Date)))))-1) * -1 ,@Date))))

If you're trying to group by a fiscal year, wrap PushSampleDatetoFiscalYear in the YEAR() function to output the fiscal year the date belongs to. To use this against a table, replace @Date with your own date field.

Updated with sample results:

SampleDate  PreviousLastSundayInJanuaryForSampleDate
2008-06-03  2008-01-27
2008-12-20  2008-01-27
2009-07-08  2009-01-25
2010-01-24  2009-01-25
2010-08-12  2010-01-31
2011-02-28  2011-01-30
2011-09-16  2011-01-30
2012-04-03  2012-01-29
2012-10-20  2012-01-29
2013-05-08  2013-01-27
2013-11-24  2013-01-27
2014-06-12  2014-01-26
2014-12-29  2014-01-26
2015-07-17  2015-01-25
2016-02-02  2016-01-31
2016-08-20  2016-01-31
2017-03-08  2017-01-29
2017-09-24  2017-01-29
2018-04-12  2018-01-28
2018-10-29  2018-01-28
2019-05-17  2019-01-27
2019-12-03  2019-01-27
2020-06-20  2020-01-26

T-SQL autopickup date

4 Answers4