Find the start and end date for the past 8 weeks

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Find the start and end date for the past 8 weeks

Author

Topic

misterdeey
Starting Member

19 Posts

Posted - 2014-01-15 : 13:15:05

Hi all,

I'm looking to get the start date and end date for the past 8 weeks knowing that my week start on Satruday and ends on Friday.

Thank you.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-15 : 14:14:29

Not 100% sure what you are after as far as results go, but maybe this will get you going?

WITH Cte (n) AS
(
	SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 
)

SELECT 
	DATEADD(DAY, (DATEDIFF(DAY, '1899-12-30', GETDATE()) / 7) * 7 + (7 * -n), '1899-12-30') AS StartDate
	,DATEADD(DAY, ((DATEDIFF(DAY, '1899-12-30', GETDATE()) / 7) * 7 + (7 * -n)) + 6, '1899-12-30') AS EndDate
FROM
	Cte

misterdeey
Starting Member

19 Posts

Posted - 2014-01-15 : 17:00:17

This is exactly what I was looking for, thank you so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources