Next WeekDay

Question

Next WeekDay

SQL 321

Hi:

I would like to get next weekday in variable (exclude Saturday and Sunday). I am using this query, but on Friday it will give me Saturday. I want it to be Monday.

DECLARE @NextDay DATE = (SELECT CAST(dateadd(d, 1, GETDATE()) AS DATE))
SELECT @NextDay

Thanks!

MelissaMa-MSFT 24,221 Reputation points

2020-10-02T01:59:28.957+00:00

Hi @SQL ，

Please remember to accept the your answer or other answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards
Melissa

4 answers

Your answer

MelissaMa-MSFT 24,221 Reputation points

2020-10-02T01:59:28.957+00:00

Hi @SQL ，

Please remember to accept the your answer or other answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards
Melissa

Answer 1

Olaf Helper 47,516

get next weekday in variable (exclude Saturday and Sunday)

What about holidays? If you want to filter then also out, create a date table, example:
https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

scott_morris-ga 16 Reputation points

2020-10-02T12:37:20.623+00:00

And what about days that are not weekends and not holidays but are also not considered "work days"? Lots of businesses have been closed for covid.

Answer 2

SQL 321

Nevermind..... I think it is this

DECLARE @CurrDay DATE ='20200925'
SELECT DATEADD(dd,CASE WHEN DATEDIFF(dd,0,@CurrDay)%7 > 3 THEN 7-DATEDIFF(dd,0,@CurrDay)%7 ELSE 1 END,@CurrDay)

Answer 3

Hi @SQL ，

Thank you so much for posting here.

Please also refer below:

DECLARE @CurrDay DATE ='20200925'  
SELECT  DATEADD(DAY, CASE DATENAME(WEEKDAY, @CurrDay)   
 WHEN 'Friday' THEN 3   
 WHEN 'Saturday' THEN 2   
 ELSE 1 END, DATEDIFF(DAY, 0, @CurrDay))

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Tom Phillips 17,771

You should use a calendar table. See "udf_AddBusinessDays":

https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/

Share via

Next WeekDay

4 answers

Your answer