SQL Year and Week numbers

Question

SQL Year and Week numbers

Viewing 5 posts - 16 through 19 (of 19 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

senthilkumar.v (8/13/2008)
select convert(char(4),datename(yyyy,'01/01/2008'))+convert(char(2),datepart (wk,'01/01/2008'))

Perfect... :hehe: Now, try that against Jenny's original request below... 😉

DECLARE @Serial CHAR(12)

SET @Serial = 'SER074400001'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike Mullen Hall of Fame Points: 3254 More actions · Answer 2

Here is an example that will compensate for the day of the week for Jan 1 of the year in question to make sure the result date is a Monday.

DECLARE @Serial CHAR(12)

SET @Serial = 'SER074400001'

--------------------------------------------------------------------

-- Set date for Monday of the Week

-- Compute days = week * 7 - 5

-- Have to allow for weekday of first of the year in calculation.

--------------------------------------------------------------------

select dateadd(day,

(convert(int,substring(@Serial,6,2)) * 7) - 5

- datepart(weekday,convert(datetime,('01/01/'+ substring(@Serial,4,2)))),

convert(datetime,('01/01/'+ substring(@Serial,4,2))))

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Since Day "0" was a Monday, I believe you'll find that the code I made also compensates for Monday. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike Mullen Hall of Fame Points: 3254 More actions · Answer 4

For the specified serial number which includes the string representing year 2007 and week 44, both solutions return a date that falls on a Monday. The first solutioin returns '11/05/07' and the second returns '10/29/07'. When these dates are converted back to the week number, the first is Monday of week 45 and the second is Monday of week 44.

set nocount on

DECLARE @Serial CHAR(12)

SET @Serial = 'SER074400001'

SELECT DATEADD(wk,DATEDIFF(wk,0,DATEADD(yy,CAST(SUBSTRING(@Serial,4,2) AS INT),'2000'))+CAST(SUBSTRING(@Serial,6,2)AS INT),0)

go

DECLARE @Serial CHAR(12)

SET @Serial = 'SER074400001'

--------------------------------------------------------------------

-- Set date for Monday of the Week

-- Compute days = week * 7 - 5

-- Have to allow for weekday of first of the year in calculation.

--------------------------------------------------------------------

select dateadd(day,

(convert(int,substring(@Serial,6,2)) * 7) - 5

- datepart(weekday,convert(datetime,('01/01/'+ substring(@Serial,4,2)))),

convert(datetime,('01/01/'+ substring(@Serial,4,2))))

go

select datepart(week,'2007-11-05') 'Week', datepart(weekday,'2007-11-05') 'Week Day'

select datepart(week,'2007-10-29') 'Week', datepart(weekday,'2007-10-29') 'Week Day'

arthur.teter Default port Points: 1400 More actions · Answer 5

Little late, but I was looking for something else and saw this one today. I run across this all the time, and was wondering why something like the following wasn't suggested. it returns an integer, but formats the same for output, and can be used for sorting.

DECLARE @MyDate datetime = '1/1/2008'

select DATEPART(year, @MyDate) * 100 + DATEPART(MONTH, @MyDate)