SQL Year and Week numbers

Question

SQL Year and Week numbers

asbains8

Say Hey Kid

Points: 690
More actions
May 14, 2008 at 4:49 am

#71592

Hi does anyone know how to create a query in SQL 2000 or 2005 to get the year and week number together for a date i.e. the 01/01/2008 would be 200801. I know how to get them seperately using datepart but i need them joined together.
Any help would be much appreciated.

Viewing 15 posts - 1 through 15 (of 19 total)

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

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 1

asbains8 (5/14/2008)
Hi does anyone know how to create a query in SQL 2000 or 2005 to get the year and week number together for a date i.e. the 01/01/2008 would be 200801. I know how to get them seperately using datepart but i need them joined together.
Any help would be much appreciated.

Is this what you want??

select convert(char(4),datename(yyyy,'01/01/2008'))+convert(char(2),datepart (wk,'01/01/2008'))

--=OR=--

select convert(char(4),'01/01/2008',112)+convert(char(2),datepart(wk,'01/01/2008'))

"-=Still Learning=-"

Lester Policarpio

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

When using DATENAME, it's already in a character format, so you don't need to use CONVERT or CAST...

SELECT DATENAME(yy,GETDATE())+RIGHT('00'+DATENAME(wk,GETDATE()),2)

--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)

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

Ack... double post.... :sick:

http://www.sqlservercentral.com/Forums/Topic500664-338-1.aspx

--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)

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 4

Jeff Moden (5/14/2008)
When using DATENAME, it's already in a character format, so you don't need to use CONVERT or CAST...
SELECT DATENAME(yy,GETDATE())+RIGHT('00'+DATENAME(wk,GETDATE()),2)

Never thought of this 😛 hehehe thanks... thats the reason i used a convert hehehe .

This seems to work too..

SELECT DATENAME(yy,GETDATE())+RIGHT(DATENAME(wk,GETDATE()),2)

Why does some members tend to double post? :hehe:

"-=Still Learning=-"

Lester Policarpio

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

Here are two more options, one returning an integer value.

declare @date datetime

select @date = '01/01/2008'

select year(@date) * 100 + datepart(week,@date)

select convert(varchar,year(@date) * 100 + datepart(week,@date))

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

Lester Policarpio (5/15/2008)
Why does some members tend to double post? :hehe:

I can't actually blame them... they look at all the different forums and think they stand a better chance of someone seeing their post if they hit more than one forum. They don't realize that most of us forum "trolls" view all the daily posts for all the forums.

--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)

jennyor SSC Enthusiast Points: 140 More actions · Answer 7

I'm trying to write an update command in sql to take the weekyear portion of a serial # and convert it back to a date by extracting the week and year "SER074400001" (0744). I can't seem to get the week to calculate back. As you can see below the closest I have come gives me (44/2007).

Any assistance would be appreciated.

update field_fact

set shipdate = substring(serln, 6, 2) + '/20' + SUBSTRING(serln, 4, 2)

--SELECT substring(serln, 6, 2) +'/20' + SUBSTRING(serln, 4, 2) AS date

FROM field_fact

WHERE (NOT (serln LIKE 'n%') AND NOT (serln LIKE '1%'))

Jenny

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

jennyor (8/11/2008)
I'm trying to write an update command in sql to take the weekyear portion of a serial # and convert it back to a date by extracting the week and year "SER074400001" (0744). I can't seem to get the week to calculate back. As you can see below the closest I have come gives me (44/2007).
Any assistance would be appreciated.
update field_fact
set shipdate = substring(serln, 6, 2) + '/20' + SUBSTRING(serln, 4, 2)
--SELECT substring(serln, 6, 2) +'/20' + SUBSTRING(serln, 4, 2) AS date
FROM field_fact
WHERE (NOT (serln LIKE 'n%') AND NOT (serln LIKE '1%'))
Jenny

Hi, Jenny, and welcome aboard!

First, just as an FYI... you'd probably do better if you posted you problem separately... it'll get more attention that way. Also, doesn't apply this time, but take a look at the link in my signature line below for when it might.

As to your problem, I think this might do what you want provided that your week starts on Monday...

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)

--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)

rgillings SSCrazy Points: 2114 More actions · Answer 9

This may be posted already, but...

DECLARE @Date DATETIME

SET @Date = GETDATE()

SELECT CONVERT(VARCHAR,DATEPART(YEAR,@Date)) + CONVERT(VARCHAR,DATEPART(WEEK,@Date))

jennyor SSC Enthusiast Points: 140 More actions · Answer 10

jennyor

SSC Enthusiast

Points: 140

August 12, 2008 at 11:01 am

#856891

Jeff, It worked like a charm thanks!

Jenny

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

Thanks for the feedback, Jenny.

--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)

Sara_DBA-629440 Right there with Babe Points: 721 More actions · Answer 12

SELECT CONVERT(VARCHAR,GetDate(),112) 😛

[font="Comic Sans MS"]Sara[/font]

A ship in the harbour is safe . . . but that's not what ships were made for. 😀

senthilkumar.v Mr or Mrs. 500 Points: 589 More actions · Answer 13

select convert(char(4),datename(yyyy,'01/01/2008'))+convert(char(2),datepart (wk,'01/01/2008'))

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

Sara_DBA (8/13/2008)
SELECT CONVERT(VARCHAR,GetDate(),112) 😛

Heh...ok, Sara... if you read Jenny's original request, tell me how your code converts the following, posted in that original request, to a date. 😛

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)