Get first date of week by week id

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)

Get first date of week by week id

Author

Topic

lleemon
Starting Member

24 Posts

Posted - 2008-08-20 : 12:42:45

I am wondering if there is a way to pass a week # and return the first day of the week. For example, the code below will return 34. Can I pass 34 into a t-sql function and get the first day of that week? So it would return 2008/08/17.


DECLARE @Date datetime
SET @Date = '2008/08/20'
SELECT DATEPART(wk, @Date)

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-20 : 12:55:38

may be this:
SELECT DATEADD(wk, DATEDIFF(wk, 6, '8/20/2008'), 6)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 12:57:23

[code]CREATE FUNCTION GetWeekStart
(
@FinancialYear int,
@Week int
)
RETURNS datetime
AS
BEGIN
DECLARE @YearStart datetime,@WeekStart datetime
SET @YearStart=DATEADD(yy,@FinancialYear,0)

SET @WeekStart=DATEADD(dd,(-1)* (DATEPART(dw,@YearStart)-1),DATEADD(wk,@Week,@YearStart))
RETURN @WeekStart
END[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-20 : 16:45:30

What happens when you pass Week 1 to the function?
How do you define a week?
How do you define the weeks surrounding new year?

E 12°55'05.25"
N 56°04'39.16"

lleemon
Starting Member

24 Posts

Posted - 2008-08-20 : 18:14:28

Ok, I just changed a little just to see what the output would look like. I set the week to 36th week and then set the FinancialYear to be this year or 2008. The result was '3908-09-06 00:00:00.000'

So I am guessing my FinancialYear value isn't right. What is the format of this value that is passed?


DECLARE @YearStart datetime,@WeekStart datetime, @Week int
SET @Week = 36
SET @YearStart=DATEADD(yy,2008,0)
SET @WeekStart=DATEADD(dd,(-1)* (DATEPART(dw,@YearStart)-1),DATEADD(wk,@Week,@YearStart))
SELECT @WeekStart

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-20 : 18:45:01

I think you need to start by giving a clear definition of week # so that we can see what your are after. Examples would also help.

Week # is something that varies from one organization to the next, so we have no way to know.

There is a standard ISO 8601 week of the year definition, but from your post, I doubt that is what you are after.

CODO ERGO SUM

lleemon
Starting Member

24 Posts

Posted - 2008-08-20 : 19:50:55

Michael,

Not sure how to answer different then my initial post,


DECLARE @Date datetime
SET @Date = '2008/08/20'
SELECT DATEPART(wk, @Date)

Code above outputs the value 34.

Overall I need to group some data by week of the year and then output the results, a nice touch would be to display the first day of that month.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-20 : 21:28:49

Not sure how to provide an answer if you can't describe what you are after and can't provide any examples.

CODO ERGO SUM

lleemon
Starting Member

24 Posts

Posted - 2008-08-21 : 08:57:12

I will try a third time.

Example:
Today is 8/21/2008. If you do datepart(wk, '2008/08/21') it tells us that it's the week is 34.

If I have the following table:

COUNT|WEEKNUM
5|32
7|33
10|34

I would like to add another field that would take the WEEKNUM value and show the first (or last day) of that week.

COUNT|WEEKNUM|FDATE
5|32|2008-08-03
7|33|2008-08-10
10|34|2008-08-17

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-21 : 09:23:09

Is January the 6th of 2008 the first day of the second week in January or the last day of the first week in January?

Jim

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-21 : 09:34:56

The value returned by datepart(wk, '2008/08/21') varies, depending on the value of DATEFIRST, so the value of the start of week will vary with that.

Instead of describing what you want in terms of code that may not do what you expect, describe your organizations definition of week. There is probably a simple way to do what you need.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources