week number (INT) to week smalldatetime

Question

week number (INT) to week smalldatetime

Rick Musa

Old Hand

Points: 374
More actions
August 2, 2006 at 8:13 am

#113122

Hello folks,
I need help with a conversion. I have a field weekno (int) , now I need to show the firstday and the lastday of that weekno.
Thanks for your help!
Rick

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Hi Rick. I'm not 100% clear on what you need here. Can you clarify by posting some sample data from the weekno column and also post an example of what you need returned.

Are you saying that your weekno column represents the # of week in a year and you want the dates for the first and last day of that week? For example, if weekno = 1 then the first day would be 1/1/2006 (Sunday Jan. 1) and the last day would be 1/7/2006 (Saturday Jan 7)??

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Rick Musa Old Hand Points: 374 More actions · Answer 2

I worked it out after some coffee and waking up!

What I have to work with was a field called Weekno, it's an int. what I needed to get is the Begining of the week (BW) and the end of the week (EW) in a date format.

weekno BW EW

10 (empyt) (empty)

so what I came up with is this:

DECLARE @week int, @year int

SELECT @year = 2006

SELECT weekno,

CONVERT(char(10),DATEADD(dw,weekno-1,CONVERT(datetime,'01/01/'+CONVERT(char(8),@Year))),101) as BW,

CONVERT(char(10),DATEADD(dw,weekno+5,CONVERT(datetime,'01/01/'+CONVERT(char(8),@Year))),101) as EW,

Agency,

Type,

PartnerName,

Callcenter,

Calls,

Res,

Rnt,

VDN

from #v_BGPartner_WK_TLS

Results:

weekno     BW             EW
1          01/01/2006      01/07/2006
2          01/02/2006      01/08/2006
3        01/03/2006      01/09/2006
4          01/04/2006      01/10/2006
There is probably a cleaner way to do this but it worked in the short!
Thanks,
Rick

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Just be aware that weeknumbers are slippery devils, and not globally the same if you don't know to which international standard, or calendar the specified number applies.

In my calendar week #1 2006 starts at 2006-01-02 and ends at 2006-01-08. 2006-01-01 is last day of week #52.

This is not what SQL server says.

/Kenneth

Ivan Christianson SSC Rookie Points: 30 More actions · Answer 4

Your result doesn't look right to me ..... can't see how week 2 starts one day later than week 1 ....

Try

SELECT weekno,

CONVERT(char(10),DATEADD(dw,(weekno-1)*7,CONVERT(datetime,'01/01/'+CONVERT(char(8),@Year))),101) as BW,

CONVERT(char(10),DATEADD(dw,(weekno-1)*7+6,CONVERT(datetime,'01/01/'+CONVERT(char(8),@Year))),101) as EW,