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 ?
Oh wait, you want last year's Friday... hold on...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.