How to calculate the next available Sunday to the following Saturday?

[dougancil]

I am writing a sql query and I'm trying to determine the next available Sunday at 12:00 AM through the next Saturday 11:59 PM and I have the following query:

select dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate ))/7)*7)+7, '17530107') AS [Sunday] from dbo.payroll
where payrollran = 'No'

That gives me the next available Sunday with no time but I still don't have the following Saturday. Can someone please assist?

Thank you

Doug

[Rabbit]

Subtract a minute from the result.

[dougancil]

Rabbit,

I'm only getting one result, and I need two. One for the next Sunday, and the second result should be the following Saturday.

[Rabbit]

For the saturday following the sunday, take the sunday datetime, add 7 days, subtract one minute.

[dougancil]

Rabbit,

Can you give me an example of that?

[Rabbit]

Your original SQL is an example of adding date values. You just need to do the same thing except add a couple of more DateAdd functions.

[dougancil]

What I don't know is how to figure out the next available Saturday. I know that what I need to do is to add another dateadd, I'm not sure about the equation to figure out the next Saturday. Also, right now it's not showing time for the Sunday, and I need that as well.

[Rabbit]

It's not showing the time? I suspect it's showing 00:00:00.000. That's a time. It's midnight.

January 2nd, 2011 is a Sunday, if I wanted the following Saturday, I would add seven days and subtract a second.

DateAdd(s, -1, DateAdd(dd, 7, '1/2/2011'))

Or if I wasn't so lazy, I would calculate the number of minutes and just add that.

[dougancil]

select dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate ))/7)*7)+7, '17530107') AS [Sunday], DateAdd(s, -1, DateAdd(dd, 7, '1/2/2011')) AS [Saturday] from dbo.payroll
where payrollran = 'No'

is what I have and that works, but if I'm determining the Sunday based on "where payrollran = 'No'" I'm not going to have the end date as part of my query, because I won't know what that is. It's based on when the next available Sunday is and then calculated from that date. So say for example, if someone enters the payroll on 2/2/2011, my query will know that the next available Sunday is 2/6/2011 and that the corresponding Saturday will be 2/12/2011.

[Rabbit]

I know. I used a fixed date but you don't have to. I only used a fixed date to simplify it.

[dougancil]

So then 'payrolldate' would be the last portion of this? Since I'm not entering a real date but going off of what the start date is?

[Rabbit]

Not payroll date, the sunday that you calculated goes there.

[dougancil]

select dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate ))/7)*7)+7, '17530107') AS [Sunday],
DateAdd(s, -1, DateAdd(dd, 7, [Sunday]) from dbo.payroll
where payrollran = 'No' ... how does the format go? Evidently this is not the way.

[Rabbit]

You can't use the field you just created in an expression. You need to put your Sunday formula there.

[dougancil]

So this entire forumla:

dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate ))/7)*7)+7, '17530107')
goes in there? OR
Would it look like this?
DateAdd(s, -1, Datediff(dd, '17530107', MAX(payrolldate ))/7)*7 +7, '17530107' from dbo.payroll

When I run it like this:

select dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate ))/7)*7)+7, '17530107') AS [Sunday],
DateAdd(s, -1, Datediff(dd, '17530107', MAX(payrolldate ))/7)*7 +7, '17530107' from dbo.payroll
where payrollran = 'No'

I get the error that says:

Invalid operator for data type. Operator equals multiply, type equals datetime.