Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Last Sunday

By Barkha Javed,

Required something reusable and could not find a SQL equivalent of the PL/SQL NEXT_DAY function.

This function returns the week end date for the specified interval.

Defined week as ending on Sunday and interval as weeks, this can be changed as needed, just more relevant to this particular Business Intelligence team.

In actual use would be aggregating several daily financial transactions to a week ending date, and limiting data for instance for the past 26 or 52 or 104 weeks, or similarly trying to capture if any data exists for future weekly intervals. Some of the reports or data extraction tools are also scheduled to execute weekly or monthly. For example the weekly promotional sales report for past and upcoming promotions executes every Wednesday, but may also be pulled earlier or later in the week if needed. Can also use the function to list Sunday the date for every week in a given time period.

Some simple examples of how to pull past or future week ending Sunday dates are listed below.



--Week ending Sunday three weeks from today
SELECT dbo.fn_LastSunday(3) as LastSunday, Convert(nchar(8),getdate(),3) as Today


--Week ending Sunday three weeks ago
SELECT dbo.fn_LastSunday(-3) as LastSunday, Convert(nchar(8),getdate(),3)as Today


--Last week's date ending on Sunday
SELECT dbo.fn_LastSunday(0) as LastSunday, Convert(nchar(8),getdate(),3) as Today


--Current week's date ending this coming Sunday
SELECT dbo.fn_LastSunday(1) as LastSunday, Convert(nchar(8),getdate(),3) as Today


Total article views: 595 | Views in the last 30 days: 1
Related Articles

get the first Sunday and last Saturday

how to get the first Sunday and last Saturday of a month





Determine Easter Sunday

Determine what day easter sunday will be based upon the year you pass the function


TechEd 2010 – Sunday

Flew out of Orlando Sunday morning via Southwest. It was my first time flying SW, I usually go with ...


Function that finds Sundays

Hey there group, I need to build a function that takes in a date and then returns the correspondi...