CODEWITHSUNDEEP
sqlsql-servert-sql
Mahdieh Reese
Mahdieh Reese

Reputation: 9

Using case statement to exclude the weekend with dateadd

I have to set a reminder date for at least 3 days before a certain date(sample-date). If that date is set on Tuesday or Wednesdays the reminder date will be set to be sent on Saturday and Sunday. I thought about setting the reminder date for Tuesdays 4 days in advance and for Wednesday 5 days in advance. This way all of my reminders will be sent during the week. I thought about using the dateadd with the case statement but not sure how to incorporate certain weekdays within my datedd function.

select dateadd (day,-3,convert(char(10),sample_date,101)) as Reminder_Sent 

-- Ideally, my query should look like something like the below: 

select 

case when dateadd (monday,-3,convert(char(10),sample_date,101)) as Monday_Reminder_Sent
when dateadd (Tuesday,-4,convert(char(10),sample_date,101)) as Tuesday_Reminder_Sent
When dateadd (Wednesday,-5,convert(char(10),sample_date,101)) as Wednesday_Reminder_Sent
when dateadd (Thursday,-3,convert(char(10),sample_date,101)) as Thursday_Reminder_Sent
Else as Reminder_Sent
End

Upvotes: 0

Views: 346

Answers (1)

Raseena Abdul
Raseena Abdul

Reputation: 536

You can use datename for comparison. Looking at your logic you will have a different calculation only for tuesday and wednesday, in which case it should return a friday instead of the weekend. All other days you need to go back 3 days.

declare  @sample_date     datetime 
set @sample_date = getdate()

select @sample_date as date , case   DATENAME ( weekday,@sample_date ) 
                when 'Tuesday' then dateadd (day,-4,convert(char(10),@sample_date ,101))
                when 'Wednesday' then dateadd (day,-5,convert(char(10),@sample_date ,101))
            else dateadd (day,-3,convert(char(10),@sample_date,101)) 
            end as Reminder_Sent

You could use DATEPART as well. But you will have to SET DATEFIRST first.

Upvotes: 1

Related Questions