First day of a given week - 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

SQL Server 2000 Forums

Transact-SQL (2000)

First day of a given week

Author

Topic

jeffbond
Starting Member

16 Posts

Posted - 2008-12-01 : 10:52:51

Hi,

How do I retrieve the date of the first day of a given week given a week number and a year number, with week starting on Sundays?

ex: week 47, year 2008
first date is 11/16/2208
last date is 11/22/2008

input(47,2008)
output '11/16/2008' 0r '2008-11-16'

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-12-01 : 11:03:01

you can use this function to help you with this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

jeffbond
Starting Member

16 Posts

Posted - 2008-12-01 : 11:18:25

Is there anything I could use inline using DatePart, DateDiff, and Convert ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 11:25:54

quote:
Originally posted by jeffbond

Hi,

How do I retrieve the date of the first day of a given week given a week number and a year number, with week starting on Sundays?

ex: week 47, year 2008
first date is 11/16/2208
last date is 11/22/2008

input(47,2008)
output '11/16/2008' 0r '2008-11-16'

DECLARE @Year varchar(4),@weeknumber int,@yeardate datetime

SET @yeardate=dateadd(yy,datediff(yy,0,@year),0)
SET @yearday=DATEPART(dw,@yeardate)

select dateadd(dd,-1* (@yearday-1),dateadd(wk,@week-1,@yeardate))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-01 : 11:33:36

How do you define a week?

E 12°55'05.63"
N 56°04'39.26"

jeffbond
Starting Member

16 Posts

Posted - 2008-12-01 : 11:49:34

first day of the first week of a year is the first Sunday before Jan 1st of that Year

sunday is first day and saturday last day

I tried your code Visak, it didn't work for me.

jeffbond
Starting Member

16 Posts

Posted - 2008-12-01 : 12:02:23

OK, I stopped being lazy and coded it :

--Set Sunday as the first Day of the week
set datefirst 7

--Input
declare @year int
set @year = 2008

declare @N int
set @N = 47

-- Declare the dates
declare @FirstDayOfYear datetime
declare @FirstDayOfFirstWeek datetime
declare @FirstDayOfWeekN datetime

-- set the dates
set @FirstDayOfYear = CONVERT(datetime,'01/01/'+CONVERT(char(4),@Year))
set @FirstDayOfFirstWeek = dateadd("d",-datepart("dw",@FirstDayOfYear)+1,@FirstDayOfYear)
set @FirstDayOfWeekN = dateadd("ww",@N-1,@FirstDayOfFirstWeek)

--show off
select @FirstDayOfWeekN

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-12-01 : 12:28:20

This does what you want no matter what the setting of datefirst is, so it is more suitable for a function:

declare @year	int
declare @N	int

set @year	= 2008
set @N		= 47

select FirstDayOfWeekN =  
	dateadd(dd,(((datediff(dd,-53684,dateadd(yy,@year-1900,0)+7)/7)*7)-7)+((@N-1)*7),-53684)


Results:
FirstDayOfWeekN
------------------------ 
2008-11-16 00:00:00.000

(1 row(s) affected)

CODO ERGO SUM

jeffbond
Starting Member

16 Posts

Posted - 2008-12-01 : 13:08:50

Thanks.

Can I ask you why your solution is better?

It doesn't seem to speed up my queries.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-12-01 : 14:05:20

quote:
Originally posted by jeffbond

Thanks.

Can I ask you why your solution is better?

It doesn't seem to speed up my queries.

It is hard to tell about speed without careful testing. This activity tends to be a small part of the overall query, so if you want to compare them, you have to prepare a test that isolates the two methods enough to detect the difference. However, it is my experience that date manipulations involving casting to a datetime from a string the way you are doing is slower that using the nested DATEADD/DATEDIFF in my solution.

Also, I just modified my original code to make it slightly shorter and eliminate one call to the DATEADD function, so this should improve the performance slightly.

As for why it is better, I stated before that it eliminates the dependency on setting of datefirst, making it more suitable for use inside a function.

CODO ERGO SUM

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-12-02 : 15:40:23

I was just messing around and ripped off part of Michael's code and came up with something that seems to work:

declare @year int
set @year = 2008

declare @N int
set @N = 47

SELECT DATEADD(DAY, (@N - 1) * 7, DATEADD(DAY,(((DATEDIFF(DAY, 0, DATEADD(YEAR, @Year - 1900, 0)) + 7) / 7) * 7)- 7, 0)) - 1

Michael, what is the purpose of the -53684?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-02 : 17:06:14

Almost right, 1st day of 1st week in 2008 is 2008-1-1 not 2007-12-30

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-02 : 17:14:14

[code]
CREATE FUNCTION DBO.FN_GETFIRSTDAYOFWEEK(@year int, @week int)
returns datetime
begin

declare @approxdate datetime

set @approxdate = dateadd(week,@week-1,dateadd(year, @year-1900,0))
if @week > 1
begin
return dateadd(dd,1-datepart(dw,@approxdate ),@approxdate )
end
return @approxdate
end
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources