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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-19 : 21:40:11
What is the 1 AD?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-03-20 : 02:47:48
When Father Christmas started work?!

Kristen
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-20 : 13:01:01
Anno DominI?

It was my very first thought of it.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page
    Next Page

- Advertisement -