SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Start of Week Function
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 03/19/2005 :  02:19:02  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 12/06/2012 00:25:26

Kristen
Test

United Kingdom
22403 Posts

Posted - 03/19/2005 :  03:18:33  Show Profile  Reply with Quote
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
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 03/19/2005 :  04:25:07  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 03/19/2005 :  13:19:13  Show Profile  Reply with Quote

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

USA
15668 Posts

Posted - 03/19/2005 :  13:23:40  Show Profile  Visit robvolk's Homepage  Reply with Quote
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)

USA
7020 Posts

Posted - 03/19/2005 :  13:36:47  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 03/20/2005 16:38:33
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 03/19/2005 :  21:40:11  Show Profile  Visit Stoad's Homepage  Reply with Quote
What is the 1 AD?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 03/20/2005 :  02:47:48  Show Profile  Reply with Quote
When Father Christmas started work?!

Kristen
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 03/20/2005 :  05:34:25  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 03/20/2005 :  06:12:04  Show Profile  Reply with Quote
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 - 03/20/2005 :  13:01:01  Show Profile  Visit Stoad's Homepage  Reply with Quote
Anno DominI?

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

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 03/20/2005 :  13:21:48  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 03/20/2005 :  15:47:11  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 03/20/2005 16:51:15
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 03/20/2005 :  18:23:18  Show Profile  Reply with Quote
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

United Kingdom
195 Posts

Posted - 03/21/2005 :  05:23:43  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 04/10/2005 :  15:00:56  Show Profile  Reply with Quote

SET DATEFIRST 1
SELECT @MidWeekDate - DATEPART(weekday, @MidWeekDate) + 1

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 Posts

Posted - 04/15/2005 :  21:12:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 05/10/2007 :  11:46:06  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 05/10/2007 :  15:29:53  Show Profile  Reply with Quote
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

USA
19 Posts

Posted - 05/10/2007 :  19:36:16  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 05/11/2007 :  00:52:46  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 05/11/2007 08:46:52
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.41 seconds. Powered By: Snitz Forums 2000