asked on SQL Select end of week date from last year
Hello EE,
if today we are 09/16/2016, I would like to be able from the same day last year to get the Friday of the week
for that example, i woud like to return 09/18/2015
if I chose 09/09/2016 i would need to return 09/11/2015
the user will always chose a date that is a friday. 09/16/2016 is a friday and 09/09/2016 as well.
i need to return the friday's date of last year
can you help me ?
if today we are 09/16/2016, I would like to be able from the same day last year to get the Friday of the week
for that example, i woud like to return 09/18/2015
if I chose 09/09/2016 i would need to return 09/11/2015
the user will always chose a date that is a friday. 09/16/2016 is a friday and 09/09/2016 as well.
i need to return the friday's date of last year
can you help me ?
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
Last Comment
Scott Pletcher
Oh wait, you want last year's Friday... hold on...
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Code below will work with any DATEFIRST setting and does not require a loop. I use a CROSS APPLY just to assign a name to the date calc -- you could instead just repeat the expression in the main code, as the second code block shows.
--more "self-documented" code
SELECT
user_date_entered,
/* back up next years max possible date to the last Friday */
DATEADD(DAY, -(DATEDIFF(DAY, Friday, prev_year_max_date) % 7), prev_year_max_date) AS last_years_Fri_date
FROM (
VALUES('20160916'),('20160
('20160910'),/*test a Saturday input date to verify the code always returns Friday*/
('20160911'),/*test a Sunday input date to verify the code always returns Friday*/
('20160912')/*test a Monday input date to verify the code always returns Friday*/
) AS test_data(user_date_entere
CROSS APPLY (
/*push the date forward 6 days so we can "back up" to Friday*/
SELECT 4 AS Friday, DATEADD(DAY, +6, DATEADD(YEAR, -1, user_date_entered)) AS prev_year_max_date
) AS assign_alias_names
--minimum amount of code
SELECT
user_date_entered,
DATEADD(DAY, -(DATEDIFF(DAY, 4,DATEADD(DAY, +6, DATEADD(YEAR, -1, user_date_entered))) % 7),
DATEADD(DAY, +6, DATEADD(YEAR, -1, user_date_entered))) AS last_years_Fri_date
FROM (
VALUES('20160916'),('20160
('20160910'),/*test a Saturday input date to verify the code always returns Friday*/
('20160911'),/*test a Sunday input date to verify the code always returns Friday*/
('20160912')/*test a Monday input date to verify the code always returns Friday*/
) AS test_data(user_date_entere
--more "self-documented" code
SELECT
user_date_entered,
/* back up next years max possible date to the last Friday */
DATEADD(DAY, -(DATEDIFF(DAY, Friday, prev_year_max_date) % 7), prev_year_max_date) AS last_years_Fri_date
FROM (
VALUES('20160916'),('20160
('20160910'),/*test a Saturday input date to verify the code always returns Friday*/
('20160911'),/*test a Sunday input date to verify the code always returns Friday*/
('20160912')/*test a Monday input date to verify the code always returns Friday*/
) AS test_data(user_date_entere
CROSS APPLY (
/*push the date forward 6 days so we can "back up" to Friday*/
SELECT 4 AS Friday, DATEADD(DAY, +6, DATEADD(YEAR, -1, user_date_entered)) AS prev_year_max_date
) AS assign_alias_names
--minimum amount of code
SELECT
user_date_entered,
DATEADD(DAY, -(DATEDIFF(DAY, 4,DATEADD(DAY, +6, DATEADD(YEAR, -1, user_date_entered))) % 7),
DATEADD(DAY, +6, DATEADD(YEAR, -1, user_date_entered))) AS last_years_Fri_date
FROM (
VALUES('20160916'),('20160
('20160910'),/*test a Saturday input date to verify the code always returns Friday*/
('20160911'),/*test a Sunday input date to verify the code always returns Friday*/
('20160912')/*test a Monday input date to verify the code always returns Friday*/
) AS test_data(user_date_entere
Just don't use that code in Europe :-) :
SET LANGUAGE ENGLISH
DECLARE @date DATETIME = '2016-09-09'
SET @date = DATEADD(YEAR, -1, @date)
WHILE DATEPART(WEEKDAY, @date) <> 6
SET @date = DATEADD(DAY, 1, @date)
SELECT @date
SET LANGUAGE GERMAN
SET @date = DATEADD(YEAR, -1, @date)
WHILE DATEPART(WEEKDAY, @date) <> 6
SET @date = DATEADD(DAY, 1, @date)
SELECT @date
SET LANGUAGE ENGLISH
DECLARE @date DATETIME = '2016-09-09'
SET @date = DATEADD(YEAR, -1, @date)
WHILE DATEPART(WEEKDAY, @date) <> 6
SET @date = DATEADD(DAY, 1, @date)
SELECT @date
SET LANGUAGE GERMAN
SET @date = DATEADD(YEAR, -1, @date)
WHILE DATEPART(WEEKDAY, @date) <> 6
SET @date = DATEADD(DAY, 1, @date)
SELECT @date
Open in new window
Just replace GETDATE() with a different date to get the Friday of that week.