Get next Friday of week - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Get next Friday of week

Author

Topic

tiss0183
Starting Member

18 Posts

Posted - 2008-05-22 : 14:43:34

I want to make a function which returns the next Friday, given a day in the week. I can't find a solution which works. For example:

Input -> Output
4/22/08 -> 4/23/08
4/23/08 -> 4/23/08
4/24/08 -> 4/30/08

I can't find a solution which works. According to one website, this should do it:
DECLARE @Friday DATETIME
SET @Friday = '20000107'
SELECT DATEADD(day, ((DATEDIFF(day, @Friday, GETDATE()) / 7) * 7), @Friday)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 14:59:54

Try this:-
SET DATEFIRST 6
SELECT DATEADD(d,7-DATEPART(dw,@Date),@Date)
Where @Date is date passed.

tiss0183
Starting Member

18 Posts

Posted - 2008-05-23 : 14:16:51

Thank you very much.

When using the code in a UDF I get this message:
Invalid use of side-effecting or time-dependent operator in 'SET COMMAND' within a function

I assume it's because SET DATEFIRST 6 is some kind of global SQLServer setting. I am using the function in a join operation.

Since the function works when taking out "SET DATEFIRST 6", is it okay to use the function as long as I don't change the DATEFIRST setting in any other code?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 14:18:30

quote:
Originally posted by tiss0183

Thank you very much.

When using the code in a UDF I get this message:
Invalid use of side-effecting or time-dependent operator in 'SET COMMAND' within a function

I assume it's because SET DATEFIRST 6 is some kind of global SQLServer setting. I am using the function in a join operation.

Since the function works when taking out "SET DATEFIRST 6", is it okay to use the function as long as I don't change the DATEFIRST setting in any other code?

Yup. As long as no other code changes the setting its ok.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources