Exclude Sundays from Date Calculation

Transact-SQL
1

Good afternoon, I'm performing a calculation between the date_submitted column and the current date to see how many days have passed. However, I want to exclude Sundays from this calculation, can this be done?

So either if the date_submitted lands on a Sunday I want to say date_submitted+1 or exclude Sundays from the calculation completely, so if the date_submitted = 05/03/2022 (Saturday) and I count the days between that date and today's date, then the answer today will be 1 and tomorrow it will be 2 because Sunday is not being counted.

I hope that makes sense. If anyone could help me with this then that would be appreciated. Thank you.

Select *
From gbr_database
Where datediff(day,importdate,getdate()) = 0 and campaign = 'GBR TEST' And 
--DebtorStatus In ('MPP 1st Pay Def Mnth 1', 'MPP High Mnth 1')
(datediff(DW,Date_Submitted,getdate()) = 1 or
datediff(DW,Date_Submitted,getdate()) = 7 or
datediff(DW,Date_Submitted,getdate()) = 14)
2

Do you have a calemdar table?

3

No, but I could create one

4

Then you could use it to exclude Sundays or another way is to use dste functions to exclude Sundays like datepart()

1 Like
5

Could you maybe explain a little more please how I do either of those 2 options if you don't mind?

6

You don't really need a calendar table or any of that other stuff. Simple mathematical calcs will do.


    DATEDIFF(DAY, Date_Submitted, GETDATE()) - 
    DATEDIFF(DAY, Date_Submitted, GETDATE()) / 7 - 
    CASE WHEN DATEDIFF(DAY, 0, Date_Submitted) % 7 + 
        DATEDIFF(DAY, Date_Submitted, GETDATE()) % 7 >= 6 
        THEN 1 ELSE 0 END AS days_not_counting_Sundays
7

Hi, Scott thanks for your reply. I have added your lines of code to my query but I'm getting an error?

"Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'."

Select *
From gbr_database
Where datediff(day,importdate,getdate()) = 0 and campaign = 'GBR Test' And 
--DebtorStatus In ('MPP 1st Pay Def Mnth 1', 'MPP High Mnth 1')
 DATEDIFF(DAY, Date_Submitted, GETDATE()) -
    DATEDIFF(DAY, Date_Submitted, GETDATE()) / 7 -
    CASE WHEN DATEDIFF(DAY, 0, Date_Submitted) % 7 + 
        DATEDIFF(DAY, Date_Submitted, GETDATE()) % 7 >= 6 
        THEN 1 ELSE 0 END AS days_not_counting_Sundays
8

Keep it simple

Datepart(DW, date_submitted) <> 7

What is your the start of your week in your locale?

Sample code

declare @simple table(date_submitted date)

insert into @simple
select top 1000 DATEADD(d,c.column_id,getdate())
from sys.all_columns c

--all Sundays
select Datepart(DW,date_submitted), * from @simple
where Datepart(DW,date_submitted) = 1


--Exclude **Sundays**
select Datepart(DW,date_submitted), * from @simple
where Datepart(DW,date_submitted) <> 1
9

I think my date start is Saturday

10

so Sunday would be 2 in your case.

11

IMO, all code you write relating to date handling should work correctly under any/all date settings. The date setting is irrelevant to the code.

1 Like
12

Sorry I still don't understand how you can count the days from the date an account was submitted and exclude Sundays from that code. I don't want to exclude any accounts where the date_submitted is a Sunday, but I do want to exclude it from the calculation of how many days have passed.between the date_submitted and todays date.

13

oops I just went by the title of your question
Exclude Sundays from date calculation.

14

If you don't want to exclude Sunday's from the results - why are you putting the calculation in the where clause?

Move that calculation into the select as a column and remove it from the where clause.

Unless - of course, you do want to exclude Sundays.

15

Hi guys thanks for your help on this, what I've decided to do is amend the date_submitted and bump it on by one day if it lands on a Sunday on the import job, that way my current code will work, and give me the results I need.