Showing dates from Sunday through Saturday

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Showing dates from Sunday through Saturday

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-01-19 : 12:39:55

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

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-01-20 : 04:19:59

You'll want these:


--previous Sunday
select dateadd(day, 1 - datepart(dw, getdate()), getdate())
--following Saturday
select dateadd(day, 7 - datepart(dw, getdate()), getdate())

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-20 : 09:59:24

Rick's queries will work for defualt US settings, but could produce incorrect results in different locales.

The queries can be normalized accross regional settings by including the @@DATEFIRST function -- while keeping the basic structure that Rick proposes.

This will work everywhere:

-- get last Sunday
SELECT dateadd(day, 1 - ((datepart(dw, getdate()) + @@DATEFIRST) % 7) , getdate())

Try these examples


set datefirst 1
-- original; produces incorrect result
select dateadd(day, 1 - datepart(dw, getdate()), getdate())
-- normailized; produces correct result
SELECT dateadd(day, 1 - ((datepart(dw, getdate()) + @@DATEFIRST) % 7) , getdate())

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-01-20 : 10:43:42

Funny, in the UK, I get the same result for both. The only time I can see them being different is if you have a different start day to the week.

-----------------------
2011-01-16 15:41:30.070

(1 row(s) affected)

-----------------------
2011-01-16 15:41:30.070

(1 row(s) affected)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-20 : 11:00:41

You're right. But did you execute the SET DATEFIRST?

dougancil
Posting Yak Master

217 Posts

Posted - 2011-01-20 : 12:54:10

Actually this is what I did:

select max(payrolldate) as payrolldate, dateadd(s, 518399, max(payrolldate))
as [sunday] from payroll

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-01-20 : 14:21:43

You can use these functions for what you want:
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
End of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760

Or code it directly:

select
	a.CurrDate,
	WeekStartingSunday	=
		dateadd(dd,(datediff(dd,-53684,a.CurrDate)/7)*7,-53684),
	WeekEndingSaturday	=
		dateadd(dd,((datediff(dd,-53684,a.CurrDate)/7)*7)+6,-53684)
from
	( -- Test Data
	select CurrDate = getdate()	union all
	select CurrDate = getdate()+1	union all
	select CurrDate = getdate()+2	union all
	select CurrDate = getdate()+3	union all
	select CurrDate = getdate()+4	union all
	select CurrDate = getdate()+5	union all
	select CurrDate = getdate()+6	union all
	select CurrDate = getdate()+7
	) a
order by
	a.CurrDate

Results:

CurrDate                WeekStartingSunday      WeekEndingSaturday
----------------------- ----------------------- -----------------------
2011-01-20 14:14:26.613 2011-01-16 00:00:00.000 2011-01-22 00:00:00.000
2011-01-21 14:14:26.613 2011-01-16 00:00:00.000 2011-01-22 00:00:00.000
2011-01-22 14:14:26.613 2011-01-16 00:00:00.000 2011-01-22 00:00:00.000
2011-01-23 14:14:26.613 2011-01-23 00:00:00.000 2011-01-29 00:00:00.000
2011-01-24 14:14:26.613 2011-01-23 00:00:00.000 2011-01-29 00:00:00.000
2011-01-25 14:14:26.613 2011-01-23 00:00:00.000 2011-01-29 00:00:00.000
2011-01-26 14:14:26.613 2011-01-23 00:00:00.000 2011-01-29 00:00:00.000
2011-01-27 14:14:26.613 2011-01-23 00:00:00.000 2011-01-29 00:00:00.000

These methods have no dependency of the setting of DATEFIRST or LANGUAGE, and they will work correctly with any setting of either.

CODO ERGO SUM

dougancil
Posting Yak Master

217 Posts

Posted - 2011-02-01 : 10:52:23

I realize as I read through this that I should be asking for the next available Sunday, not the last one previously, through the next Saturday night at 12:00 PM. I've tried all of these queries, and they seem to produce the previous Sunday.

dougancil
Posting Yak Master

217 Posts

Posted - 2011-02-01 : 10:56:40

here is the query as it stands now.

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

I only see the Date for the next available Sunday ... and I need the time as well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources