How to show dates from Sunday through Saturday?

[dougancil]

I have the following query:

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

and it's currently showing me from Sunday at 12:00 am through the following Sunday at 12:00 am and I need it to show me from Sunday at midnight to Saturday at midnight. I've tried to adjust this but I am obviously missing something. Can someone please assist me on this?

Thanks

Doug

[Rabbit]

You do realize that if you go from Sunday 12 AM to Saturday 12 AM that you lose everything that happens on Saturday right? Currently you overlap by one minute. If you use Saturday 12 AM then you lose 23 hours and 59 minutes and 59 seconds of data. If you the overlap is unacceptable but you want to keep the rest of the data, you can't use "midnight" as you call it. You have to just subtract one second from the date you currently have.

[dougancil]

Rabbit,

So then if I want to remove that one second, how do I go about doing that? I'm presenting this information to end users and I think that they would be confused by the time span showing Sunday to Sunday. I think if I showed them Sunday to Saturday at 11:59:59 then they wouldn't "freak" out about it.

[Rabbit]

Well, you already have the DateAdd function. Use another one to "add" -1 seconds.

[ck9663]

Are you going to use the range in a filter condition?

~~ CK