How to get the last sunday of the year using TSQL?

Question

I need to check if a given day is the last sunday of any year, if yes the return 1 using TSQL only.

I do not have much idea about TSQL.

Possible duplicate of [Find last sunday](https://stackoverflow.com/questions/1794697/find-last-sunday) — Tab Alleman, May 10 '18 at 13:19

Gordon Linoff · Answer 1 · 2018-05-11T02:11:05.780

2

SQL Server has a problem with weekdays, because they can be affected by internationalization settings. Assuming the defaults, you can do:

select dateadd(day,
               1 - datepart(weekday, datefromparts(@year, 12, 31)),
               datefromparts(@year, 12, 31)
              )

Otherwise, you'll need to do a case expression to turn the day of the week into a number.

In an older version of SQL Server, you could do:

select dateadd(day,
               1 - datepart(weekday, cast(@year + '0101' as date)),
               cast(@year + '0101' as date)
              )

edited May 11 '18 at 02:11

answered May 10 '18 at 12:36

Gordon Linoff

1,242,037
58
646
786

i daeclared year as int and then tried above query and it says cannot construct data type date , some of the arguments have values which are not valid @Gordon – abhishek yadav May 10 '18 at 12:47
Seems to work fine here. What version of SQL Server? – Jacob H May 10 '18 at 13:17

score 1 · Answer 2 · answered May 10 '18 at 12:41

1

I haven't worked with tsql specifically but if my sql knowledge and googling is good enough then something like this should do the trick:

... WHERE DATEPART(dw, date) = 7 and DATEDIFF (d, date, DATEFROMPARTS (DATEPART(yyyy, date), 12, 31)) <= 6

Basically we check if that day is Sunday at first and then if it's less than week away from last day of the year

answered May 10 '18 at 12:41

NoOorZ24

2,914
1
14
33

Nice, simple solution. – EzLo May 10 '18 at 13:01

score 0 · Answer 3 · answered May 10 '18 at 12:53

Using Mr. Gordon's query, following IIF() returns 1 if given day is last Sunday of the year, returns 0 if it is not.

Using 2018 as year and 2018-12-30 as given date. You can replace values with variables.

select IIF( DATEDIFF(DAY,'2018-12-30',
              DATEADD(day,
              1 - datepart(weekday, datefromparts(2018, 12, 31)),
              datefromparts(2018, 12, 31)
                   )) = 0, 1, 0)

score 0 · Answer 4 · answered May 10 '18 at 13:09

You can use this function

Function Code :

    create FUNCTION CheckIsSaturday 
(
    @date DATETIME
)
RETURNS int
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result INT
    DECLARE @DayOfWeek NVARCHAR(22)
    DECLARE @LastDayOfYear DATETIME
    select @LastDayOfYear=DATEADD(yy, DATEDIFF(yy, 0, @date) + 1, -1)
    SELECT @DayOfWeek=DATENAME(dw, @date)
    IF(@DayOfWeek='Saturday' AND DATEDIFF(dd,@date,@LastDayOfYear)<7)
        RETURN 1;
    RETURN 0; 

END
GO

function Usage:

SELECT dbo.CheckIsSaturday('2017-12-23')

score 0 · Answer 5 · answered May 10 '18 at 13:20

This becomes quite trivial if you have a Calendar Table

DECLARE @CheckDate DATE = '20181230'

;WITH cteGetDates AS 
(
    SELECT
         [Date], WeekDayName, WeekOfMonth, [MonthName], [Year]
        ,LastDOWInMonth = ROW_NUMBER() OVER 
        (
            PARTITION BY FirstDayOfMonth, [Weekday] 
            ORDER BY [Date] DESC
        )
    FROM
        dbo.DateDimension
)
SELECT * FROM cteGetDates D
WHERE D.LastDOWInMonth = 1 AND D.WeekDayName = 'Sunday' and D.MonthName = 'December' AND D.[Date] = @CheckDate

score 0 · Answer 6 · answered May 11 '18 at 08:16

You can also use this one to get every last day of the year:

;WITH getlastdaysofyear ( LastDay, DayCnt ) AS ( 
   SELECT   DATEADD(dd, -DAY(DATEADD(mm, 1,  DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1))),
            DATEADD(mm, 1,  DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1))),
            0 AS DayCnt
   UNION ALL
   SELECT   LastDay,
   DayCnt + 1
   FROM     getlastdaysofyear
)
   SELECT  *
        FROM ( SELECT TOP 7 DATEADD(DD, -DayCnt, LastDay) LastDate,
                      'Last ' + DATENAME(Weekday,DATEADD(DD, -DayCnt, LastDay)) AS DayStatus
   FROM      getlastdaysofyear ) T

ORDER BY DATEPART(Weekday, LastDate)

Hope you like it :)

How to get the last sunday of the year using TSQL?

6 Answers6