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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  General SQL Server Forums  Script Library  Start of Week Function Reply to Topic  Printer Friendly
Author  Topic
Page: of 3

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

 Posted - 03/19/2005 :  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.000If the day passed is Monday, 2005-03-14, and Sunday is the start of the week, it returns: 2005-03-13 00:00:00.000If the day passed is Monday, 2005-03-14, and Monday is the start of the week, it returns: 2005-03-14 00:00:00.000Does 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=64760There are other Start of Time Period Functions posted here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755There 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 Edited by - Michael Valentine Jones on 12/06/2012 00:25:26

Kristen
Test

United Kingdom
22859 Posts

 Posted - 03/19/2005 :  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 Edited by - Kristen on 03/19/2005 03:19:14

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

 Posted - 03/19/2005 :  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..DavidMA front-end is something that tries to violate a back-end.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

 Posted - 03/19/2005 :  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 KristenWouldn't SET DATEFIRST @WEEK_START_DAY SELECT @Something = DATEPART(weekday, @DATE) help with this process? (Note that SET DATEFIRST uses 1=Monday)KristenCODO ERGO SUM

robvolk
Most Valuable Yak

USA
15732 Posts

 Posted - 03/19/2005 :  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)

USA
7020 Posts

 Posted - 03/19/2005 :  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 byrmolKristen,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..DavidMA front-end is something that tries to violate a back-end.CODO ERGO SUM Edited by - Michael Valentine Jones on 03/20/2005 16:38:33

Freaky Yak Linguist

*
1983 Posts

 Posted - 03/19/2005 :  21:40:11 What is the 1 AD?

Kristen
Test

United Kingdom
22859 Posts

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

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

 Posted - 03/20/2005 :  05:34:25 AD: Anno Domino (year when J was born)BC: Before ChristAnd funnily there is no Year 0rockmoose

Kristen
Test

United Kingdom
22859 Posts

 Posted - 03/20/2005 :  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

Freaky Yak Linguist

*
1983 Posts

 Posted - 03/20/2005 :  13:01:01 Anno DominI?It was my very first thought of it.

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

 Posted - 03/20/2005 :  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)

USA
7020 Posts

 Posted - 03/20/2005 :  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 rockmooseStupid 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 ?rockmooseCODO ERGO SUM Edited by - Michael Valentine Jones on 03/20/2005 16:51:15

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

 Posted - 03/20/2005 :  18:23:18 Thank You mike,I will start to use that wikipedia :)Yak DBA Kernel ( hehehe, nice )rockmoose

gpl
Posting Yak Master

United Kingdom
195 Posts

 Posted - 03/21/2005 :  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

United Kingdom
22859 Posts

 Posted - 04/10/2005 :  15:00:56 ``` SET DATEFIRST 1 SELECT @MidWeekDate - DATEPART(weekday, @MidWeekDate) + 1 ```would do that too - but you can't have the SET DATEFIRST in a functionKristen

ypli88@hotmail.com
Starting Member

1 Posts

 Posted - 04/15/2005 :  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 12week7(4/03/05 - 4/09/05) 5 6 3week6(3/27/05 - 4/02/05) 7 8 10.....week1(2/20/05 - 2/26/05) 8 5 9I 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 Weekfrom casewhere datediff(week, Received_Date_Time, GETDATE())<8group by status, Received_Date_Timeplease help me, thanksbetty

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2052 Posts

 Posted - 05/10/2007 :  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 DATEFIRSTSELECT 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)

USA
7020 Posts

 Posted - 05/10/2007 :  15:29:53 quote:Originally posted by elwoosI 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 DATEFIRSTSELECT 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

USA
19 Posts

 Posted - 05/10/2007 :  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)

USA
7020 Posts

 Posted - 05/11/2007 :  00:52:46 quote:Originally posted by herothecatHere'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 Edited by - Michael Valentine Jones on 05/11/2007 08:46:52
Page: of 3  Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC