Start of Week Function - SQL Server Forums

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

General SQL Server Forums

Script Library

Start of Week Function

Author

Topic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-19 : 02:19:02

I wrote the following function to find the start of week date for a given date and a given start day of week.

For example:
If the day passed is Saturday, 2005-03-19, and Sunday is the start of the week, it returns: 2005-03-13 00:00:00.000

If the day passed is Monday, 2005-03-14, and Sunday is the start of the week, it returns: 2005-03-13 00:00:00.000

If the day passed is Monday, 2005-03-14, and Monday is the start of the week, it returns: 2005-03-14 00:00:00.000

Does anyone have a simpler algorithim for start of week that they care to post?

Edit (2006/4/15):
Posted a companion function F_END_OF_WEEK, on this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760

There are other Start of Time Period Functions posted here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

There are other End Date of Time Period Functions here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759


create function dbo.F_START_OF_WEEK
(
	@DATE			datetime,
	-- Sun = 1, Mon = 2, Tue = 3, Wed = 4
	-- Thu = 5, Fri = 6, Sat = 7
	-- Default to Sunday
	@WEEK_START_DAY		int	= 1	
)
/*
Find the fisrt date on or before @DATE that matches 
day of week of @WEEK_START_DAY.
*/
returns		datetime
as
begin
declare	 @START_OF_WEEK_DATE	datetime
declare	 @FIRST_BOW		datetime

-- Check for valid day of week
if @WEEK_START_DAY between 1 and 7
	begin
	-- Find first day on or after 1753/1/1 (-53690)
	-- matching day of week of @WEEK_START_DAY
	-- 1753/1/1 is earliest possible SQL Server date.
	select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))
	-- Verify beginning of week not before 1753/1/1
	if @DATE >= @FIRST_BOW
		begin
		select @START_OF_WEEK_DATE = 
		dateadd(dd,(datediff(dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)
		end
	end

return @START_OF_WEEK_DATE

end
go

-- Sample function calls

select dbo.F_START_OF_WEEK(getdate(),default) -- Returns Date for Sunday

select dbo.F_START_OF_WEEK(getdate(),1)	-- Returns Date for Sunday
select dbo.F_START_OF_WEEK(getdate(),2)	-- Returns Date for Monday
select dbo.F_START_OF_WEEK(getdate(),3)	-- Returns Date for Tuesday
select dbo.F_START_OF_WEEK(getdate(),4)	-- Returns Date for Wednesday
select dbo.F_START_OF_WEEK(getdate(),5)	-- Returns Date for Thursday
select dbo.F_START_OF_WEEK(getdate(),6)	-- Returns Date for Friday
select dbo.F_START_OF_WEEK(getdate(),7)	-- Returns Date for Saturday

select dbo.F_START_OF_WEEK(getdate(),0)	-- Returns NULL
select dbo.F_START_OF_WEEK(getdate(),8)	-- Returns NULL

Edited 2005/4/9:

I thought I would also post an alternate way of doing the Start of Week instead of using the F_START_OF_WEEK function. These queries demo doing the Start of Week inline in a query, and use a similar algorithm to find the start of week, but the start day of week is hard coded.

I posted two versions. The first version is simpler, but it has a minor flaw that returns a false result if the start of week would be before 1753/1/1. For the vast majority of applications this would not be a problem. In the second, the algorithm is modified slightly to cause it to overflow if you pick a date that would result in a start of week before 1753/1/1. Note that the F_START_OF_WEEK function returns a NULL in this situation.

The demo queries use the F_TABLE_NUMBER_RANGE that is posted in another thread in order to generate dates to demonstrated the results:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685



--  First demo query for Start of Week
--  Returns bad result if the start of week would be before 1753/1/1
select
	DATE,
	Sun = dateadd(dd,(datediff(dd,-53684,a.DATE)/7)*7,-53684),
	Mon = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690),
	Tue = dateadd(dd,(datediff(dd,-53689,a.DATE)/7)*7,-53689),
	Wed = dateadd(dd,(datediff(dd,-53688,a.DATE)/7)*7,-53688),
	Thu = dateadd(dd,(datediff(dd,-53687,a.DATE)/7)*7,-53687),
	Fri = dateadd(dd,(datediff(dd,-53686,a.DATE)/7)*7,-53686),
	Sat = dateadd(dd,(datediff(dd,-53685,a.DATE)/7)*7,-53685)
from
	(
	select
		DATE = convert(datetime,number)
	from
		F_TABLE_NUMBER_RANGE(36524,40000)
	) a


--  Second demo query for Start of Week
--  Modified to cause an error instead of returning a bad date
--  if the start of week would be before 1753/1/1
select
	DATE,
	Sun = dateadd(dd,((datediff(dd,-53684,a.DATE+7)/7)*7)-7,-53684),
	Mon = dateadd(dd,((datediff(dd,-53690,a.DATE+7)/7)*7)-7,-53690),
	Tue = dateadd(dd,((datediff(dd,-53689,a.DATE+7)/7)*7)-7,-53689),
	Wed = dateadd(dd,((datediff(dd,-53688,a.DATE+7)/7)*7)-7,-53688),
	Thu = dateadd(dd,((datediff(dd,-53687,a.DATE+7)/7)*7)-7,-53687),
	Fri = dateadd(dd,((datediff(dd,-53686,a.DATE+7)/7)*7)-7,-53686),
	Sat = dateadd(dd,((datediff(dd,-53685,a.DATE+7)/7)*7)-7,-53685)
from
	(
	select
		DATE = convert(datetime,number)
	from
		F_TABLE_NUMBER_RANGE(36524,40000)
	) a


Edit: 2012-12-06
--  Added third demo query for Start of Week
--  Modified to return NULL if the start of week would be before 1753/1/1
select
	a.DATE,
	[DayOfWeek] = left(datename(dw,a.DATE),9),
	Mon =	dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690),
	Tue =	case when a.DATE >= -53689 then 
		dateadd(dd,(datediff(dd,-53689,a.DATE)/7)*7,-53689) end,
	Wed =	case when a.DATE >= -53688 then 
		dateadd(dd,(datediff(dd,-53688,a.DATE)/7)*7,-53688) end,
	Thu =	case when a.DATE >= -53687 then 
		dateadd(dd,(datediff(dd,-53687,a.DATE)/7)*7,-53687) end,
	Fri =	case when a.DATE >= -53686 then
		dateadd(dd,(datediff(dd,-53686,a.DATE)/7)*7,-53686) end,
	Sat =	case when a.DATE >= -53685 then
		dateadd(dd,(datediff(dd,-53685,a.DATE)/7)*7,-53685) end,
	Sun =	case when a.DATE >= -53684 then
		dateadd(dd,(datediff(dd,-53684,a.DATE)/7)*7,-53684) end
from
	(-- Test Data
	select Date = convert(datetime,'17530101')	union all
	select Date = convert(datetime,'17530102')	union all
	select Date = convert(datetime,'17530103')	union all
	select Date = convert(datetime,'17530104')	union all
	select Date = convert(datetime,'17530105')	union all
	select Date = convert(datetime,'17530106')	union all
	select Date = convert(datetime,'17530107')	union all
	select Date = convert(datetime,'17530108')	union all
	select Date = convert(datetime,'99991224')	union all
	select Date = convert(datetime,'99991225')	union all
	select Date = convert(datetime,'99991226')	union all
	select Date = convert(datetime,'99991227')	union all
	select Date = convert(datetime,'99991228')	union all
	select Date = convert(datetime,'99991229')	union all
	select Date = convert(datetime,'99991230')	union all
	select Date = convert(datetime,'99991231')	union all
		select
		DATE = convert(datetime,number)
	from
		F_TABLE_NUMBER_RANGE(36524,42000)
	) a
order by
	a.DATE

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2005-03-19 : 03:18:33

Wouldn't

SET DATEFIRST @WEEK_START_DAY
SELECT @Something = DATEPART(weekday, @DATE)

help with this process? (Note that SET DATEFIRST uses 1=Monday)

Kristen

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-03-19 : 04:25:07

Kristen,

IIRC, DATEPART is a non-deterministic function and thus will roayally screw any chance of using it in computed columns, indexed views etc..

I heard date support is better in 2005 ie. Goes to 1AD..

DavidM

A front-end is something that tries to violate a back-end.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-19 : 13:19:13

I stayed away from using @@DATEFIRST, DATEPART, and SET DATEFIRST for a couple of reasons.

The first is that it wasn't clear to me from BOL just what the scope of SET DATEFIRST is. Does it only have scope within the function, or would it change the @@DATEFIRST setting for the current connection? I didn’t want anything that might cause a calling procedure to behave in an unpredictable way, and I didn’t want to make any assumption about the setting of @@DATEFIRST.

The second reason was that I wasn't really sure how I could use it to make a simpler algorithm. Although the algorithm I used isn't particularly intuitive, the DATEADD and DATEDIFF functions in combination with the /, *, and % operators is fairly straight forward and fast, and also takes care of setting the time to midnight.

However, I can’t escape the feeling that there is something simpler out there, which is why I posted this.

quote:
Originally posted by Kristen

Wouldn't

SET DATEFIRST @WEEK_START_DAY
SELECT @Something = DATEPART(weekday, @DATE)

help with this process? (Note that SET DATEFIRST uses 1=Monday)

Kristen

CODO ERGO SUM

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-19 : 13:23:40

quote:
I heard date support is better in 2005 ie. Goes to 1AD..

They've dropped the extended date, time, and UTCdatetime types from 2005, as of Beta 2, so you'd have to create a user-defined type for this. The CLR date types should be supported though, so it should be pretty easy to do.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-19 : 13:36:47

That’s interesting about support from 1 AD forward in SQL Server 2005. I wonder how they handle the transition to the Gregorian calendar? Do they just assume that it was in use from 1 AD forward? Especially since it was adopted at different times in different parts of the world. I assume that the reason for the current January 1, 1753 start date in SQL Server 2000 is that the Gregorian calendar was adopted in the British Empire, including America, in September of 1752.

Sounds like it could foul up the assumptions in the dbo.F_START_OF_WEEK function if the relative date of 0 = January 1, 1900 changes, or if there is just a completely different way of handling dates. Or would that be supported only with new data types, and not affect DATETIME?

quote:
Originally posted by byrmol

Kristen,

IIRC, DATEPART is a non-deterministic function and thus will roayally screw any chance of using it in computed columns, indexed views etc..

I heard date support is better in 2005 ie. Goes to 1AD..

DavidM

A front-end is something that tries to violate a back-end.

CODO ERGO SUM

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-19 : 21:40:11

What is the 1 AD?

Kristen
Test

22859 Posts

Posted - 2005-03-20 : 02:47:48

When Father Christmas started work?!

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-20 : 05:34:25

AD: Anno Domino (year when J was born)
BC: Before Christ
And funnily there is no Year 0

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-03-20 : 06:12:04

I always wondered about that ... for JC to have a first birthday in AD1 he would have had to have been born Before Christ!

Kristen

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-20 : 13:01:01

Anno DominI?

It was my very first thought of it.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-20 : 13:21:48

Stupid Q:
Where does the "Christ" part come from when we refer to J ?
Is that something that was appended to J's name by the church at a later date ?

>> Anno DominI?
Was that a question, or a correction ?

rockmoose

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-20 : 15:47:11

This thread has wandered pretty far from the Start of Week Function.

From http://en.wikipedia.org/wiki/Messiah

"In Judaism, the Messiah is a human descendant of King David who will rebuild the nation of Israel and bring world peace by restoring the Davidic Kingdom. Christians consider Jesus to be that messiah (in Greek Christ), as well as the son of God and a member of the Holy Trinity. The word Christ (Greek ×ñéóôüò, Khristos, "the anointed one") is a literal translation of "mashiach". In Islam, Jesus is also considered the Masiha, or Messiah, and his eventual return to the Earth is expected with that of another messianic figure, the Mahdi.

The Septuagint, an ancient Jewish translation of the Old Testament into Greek, translates all thirty-nine instances of the word messiah as Khristos. The New Testament records the Greek form, Messias, only twice, in John 1:41 and 4:25."

quote:
Originally posted by rockmoose

Stupid Q:
Where does the "Christ" part come from when we refer to J ?
Is that something that was appended to J's name by the church at a later date ?

>> Anno DominI?
Was that a question, or a correction ?

rockmoose

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-20 : 18:23:18

Thank You mike,
I will start to use that wikipedia :)

Yak DBA Kernel ( hehehe, nice )

rockmoose

gpl
Posting Yak Master

195 Posts

Posted - 2005-03-21 : 05:23:43

This is a function I came up with as so many queries I produced needed to be done by week commencing.

It returns the Monday date for any given date; it should cope with any DateFirst setting (but happy to be proved wrong)

Graham


CREATE FUNCTION dbo.WeekCommence (@MidWeekDate DateTime)
RETURNS DateTime AS

BEGIN
DECLARE @WeekCommence DateTime
	SET @WeekCommence = DateAdd(d, -((@@DATEFIRST + DatePart(dw, @MidWeekDate) -2) % 7), @MidWeekDate)
	RETURN @WeekCommence
END

Kristen
Test

22859 Posts

Posted - 2005-04-10 : 15:00:56

[code]
SET DATEFIRST 1
SELECT @MidWeekDate - DATEPART(weekday, @MidWeekDate) + 1
[/code]
would do that too - but you can't have the SET DATEFIRST in a function

Kristen

ypli88@hotmail.com
Starting Member

1 Post

Posted - 2005-04-15 : 21:12:49

I have a table - "Case" that contains caseNumber, status, and received_Date_Time. I want to run a query to show the case status on a week by week basis for last eight weeks from system date. for example:
I want to output something like this
-------------------------- NEW WORKING CLOSED
week8(4/10/05 - 4/16/05) 2 4 12
week7(4/03/05 - 4/09/05) 5 6 3
week6(3/27/05 - 4/02/05) 7 8 10
.....
week1(2/20/05 - 2/26/05) 8 5 9

I used following code, but the 'group by' doesn't work the way i want it to be.
select status, count(status) as CaseCount, (datediff(week, Received_Date_Time, GETDATE())) + 1 as Week
from case
where datediff(week, Received_Date_Time, GETDATE())<8
group by status, Received_Date_Time

please help me, thanks
betty

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2007-05-10 : 11:46:06

I am using this to work out the previous monday's date. If you run it on a monday it shows that days date. It would be easy to tweak and it doesn't rely on DATEFIRST

SELECT DATEADD(d,-1 * cast(GETDATE() as integer) % 7,GETDATE())

(better late than never eh?)

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-10 : 15:29:53

quote:
Originally posted by elwoos

I am using this to work out the previous monday's date. If you run it on a monday it shows that days date. It would be easy to tweak and it doesn't rely on DATEFIRST

SELECT DATEADD(d,-1 * cast(GETDATE() as integer) % 7,GETDATE())

(better late than never eh?)

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

I don't think your code does what you hoped.

This code find's last Mondays date using my function, and shows the results for your code for the same datetime value.

The F_START_OF_WEEK function is designed to find the first Monday on or before the date passed to it when you ask for Monday. To find Monday of last week, just subtract one day from the current date, and pass that to the F_START_OF_WEEK function.


select
	DT,
	Last_Weeks_Monday =
	DBO.F_START_OF_WEEK(DT-1,2),
	Elwoos_Code =
	dateadd(d,-1*cast(DT as integer)%7,DT)
from
	(
	select
		DT = dateadd(dd,number,'20070507 00:00')
	from
		F_TABLE_NUMBER_RANGE(-7,0)
	union all
	select
		DT = dateadd(dd,number,'20070507 13:00')
	from
		F_TABLE_NUMBER_RANGE(-7,0)
	) a
order by
	DT

Results:

DT                                                     Last_Weeks_Monday                                      Elwoos_Code
------------------------------------------------------ ------------------------------------------------------ -----------------------
2007-04-30 00:00:00.000                                2007-04-23 00:00:00.000                                2007-04-30 00:00:00.000
2007-04-30 13:00:00.000                                2007-04-23 00:00:00.000                                2007-04-29 13:00:00.000
2007-05-01 00:00:00.000                                2007-04-30 00:00:00.000                                2007-04-30 00:00:00.000
2007-05-01 13:00:00.000                                2007-04-30 00:00:00.000                                2007-04-29 13:00:00.000
2007-05-02 00:00:00.000                                2007-04-30 00:00:00.000                                2007-04-30 00:00:00.000
2007-05-02 13:00:00.000                                2007-04-30 00:00:00.000                                2007-04-29 13:00:00.000
2007-05-03 00:00:00.000                                2007-04-30 00:00:00.000                                2007-04-30 00:00:00.000
2007-05-03 13:00:00.000                                2007-04-30 00:00:00.000                                2007-04-29 13:00:00.000
2007-05-04 00:00:00.000                                2007-04-30 00:00:00.000                                2007-04-30 00:00:00.000
2007-05-04 13:00:00.000                                2007-04-30 00:00:00.000                                2007-04-29 13:00:00.000
2007-05-05 00:00:00.000                                2007-04-30 00:00:00.000                                2007-04-30 00:00:00.000
2007-05-05 13:00:00.000                                2007-04-30 00:00:00.000                                2007-04-29 13:00:00.000
2007-05-06 00:00:00.000                                2007-04-30 00:00:00.000                                2007-04-30 00:00:00.000
2007-05-06 13:00:00.000                                2007-04-30 00:00:00.000                                2007-05-06 13:00:00.000
2007-05-07 00:00:00.000                                2007-04-30 00:00:00.000                                2007-05-07 00:00:00.000
2007-05-07 13:00:00.000                                2007-04-30 00:00:00.000                                2007-05-06 13:00:00.000

(16 row(s) affected)

CODO ERGO SUM

herothecat
Starting Member

19 Posts

Posted - 2007-05-10 : 19:36:16

Here's what I use. IMHO it's easier to read and when run on 1,000,000 records it's about 9% faster.

I changed my variable to match yours and included the check for a valid start day number (Thanks)

Let me know what you think......


CREATE FUNCTION [dbo].[firstOfWeek]
    (@DATE DATETIME 
    ,@WEEK_START_DAY INT = 1)

RETURNS DATETIME 

    /*  @date = date to calculate
        @WEEK_START_DAY = the dayNumber of the first day of the week

	    Sun = 1, Mon = 2, Tue = 3, Wed = 4
	    Thu = 5, Fri = 6, Sat = 7
	    Default to Sunday
    */
AS
BEGIN
    	
    DECLARE @START_OF_WEEK_DATE DATETIME

    -- Check for valid day of week
    IF @WEEK_START_DAY between 1 and 7
        BEGIN	
            SET 
                @START_OF_WEEK_DATE = 
                    CASE WHEN @WEEK_START_DAY - datepart(dw,@DATE) > 0 THEN
                        dateadd(day, @WEEK_START_DAY - datepart(dw,@DATE) - 7, @DATE)
                    ELSE
                        dateadd(day, @WEEK_START_DAY - datepart(dw,@DATE), @DATE)
                    END         
		END

    RETURN @START_OF_WEEK_DATE

END

Why push the envelope when you can just open it?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-11 : 00:52:46

quote:

Originally posted by herothecat

Here's what I use. IMHO it's easier to read and when run on 1,000,000 records it's about 9% faster.

I changed my variable to match yours and included the check for a valid start day number (Thanks)

Let me know what you think......


CREATE FUNCTION [dbo].[firstOfWeek]
    (@DATE DATETIME 
    ,@WEEK_START_DAY INT = 1)

RETURNS DATETIME 

    /*  @date = date to calculate
        @WEEK_START_DAY = the dayNumber of the first day of the week

	    Sun = 1, Mon = 2, Tue = 3, Wed = 4
	    Thu = 5, Fri = 6, Sat = 7
	    Default to Sunday
    */
AS
BEGIN
    	
    DECLARE @START_OF_WEEK_DATE DATETIME

    -- Check for valid day of week
    IF @WEEK_START_DAY between 1 and 7
        BEGIN	
            SET 
                @START_OF_WEEK_DATE = 
                    CASE WHEN @WEEK_START_DAY - datepart(dw,@DATE) > 0 THEN
                        dateadd(day, @WEEK_START_DAY - datepart(dw,@DATE) - 7, @DATE)
                    ELSE
                        dateadd(day, @WEEK_START_DAY - datepart(dw,@DATE), @DATE)
                    END         
		END

    RETURN @START_OF_WEEK_DATE

END

Why push the envelope when you can just open it?

Your function does not produce correct results if the setting of DATEFIRST is anything other than 7.


set datefirst 1
-- Should return Monday, 2007-05-07
select Date = [dbo].[firstOfWeek]('20070511',2)

Result:
Date                                                   
------------------------------------------------------ 
2007-05-08 00:00:00.000

(1 row(s) affected)

Your function produces an error with some dates, while my function is designed to return a NULL when the date returned would be before 1753-01-01.


SET DATEFIRST 7
select [dbo].[firstOfWeek]('17530101',3)

Server: Msg 517, Level 16, State 1, Procedure firstOfWeek, Line 23
Adding a value to a 'datetime' column caused overflow.

CODO ERGO SUM

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources