Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Year and Week numbers Expand / Collapse
Author
Message
Posted Wednesday, May 14, 2008 4:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:51 AM
Points: 16, 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.
Post #500305
Posted Wednesday, May 14, 2008 7:53 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:06 PM
Points: 398, Visits: 2,428
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

Post #500968
Posted Wednesday, May 14, 2008 10:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:04 AM
Points: 35,546, Visits: 32,140
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #500990
Posted Wednesday, May 14, 2008 10:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:04 AM
Points: 35,546, Visits: 32,140
Ack... double post....

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #500992
Posted Thursday, May 15, 2008 12:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:06 PM
Points: 398, Visits: 2,428
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 :P 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?


"-=Still Learning=-"

Lester Policarpio

Post #501049
Posted Thursday, May 15, 2008 8:46 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, November 14, 2014 7:43 AM
Points: 1,792, Visits: 726
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))
Post #501374
Posted Thursday, May 15, 2008 5:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:04 AM
Points: 35,546, Visits: 32,140
Lester Policarpio (5/15/2008)
Why does some members tend to double post?


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #501718
Posted Monday, August 11, 2008 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 3, 2009 6:25 AM
Points: 2, 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
Post #550280
Posted Monday, August 11, 2008 7:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:04 AM
Points: 35,546, Visits: 32,140
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #550687
Posted Tuesday, August 12, 2008 6:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 9, 2014 4:10 PM
Points: 1,388, 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))
Post #550885
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse