SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Year and Week numbers


SQL Year and Week numbers

Author
Message
asbains8
asbains8
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 254
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.
Lester Policarpio
Lester Policarpio
Say Hey Kid
Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)

Group: General Forum Members
Points: 679 Visits: 2459
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
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88268 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88268 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lester Policarpio
Lester Policarpio
Say Hey Kid
Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)

Group: General Forum Members
Points: 679 Visits: 2459
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 Tongue 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
Mike Mullen
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2320 Visits: 773
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
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88268 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jennyor
jennyor
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 15
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
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88268 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
rgillings
rgillings
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1424 Visits: 239
This may be posted already, but...


DECLARE @Date DATETIME
SET @Date = GETDATE()
SELECT CONVERT(VARCHAR,DATEPART(YEAR,@Date)) + CONVERT(VARCHAR,DATEPART(WEEK,@Date))
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search