Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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: 573 | 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





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...


Calculate gregorian easter sunday using a table valued function

Calculate gregorian easter sunday using a table valued function.


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones