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 Tuesday, August 12, 2008 11:01 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
Jeff, It worked like a charm thanks!
Jenny
Post #551243
Posted Tuesday, August 12, 2008 6:12 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 37,076, Visits: 31,637
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."

(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 #551503
Posted Wednesday, August 13, 2008 12:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, September 28, 2008 11:25 PM
Points: 19, Visits: 93
SELECT CONVERT(VARCHAR,GetDate(),112) :P

Sara

A ship in the harbour is safe . . . but that's not what ships were made for. :D
Post #551629
Posted Wednesday, August 13, 2008 2:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, May 2, 2014 1:27 AM
Points: 430, Visits: 109
select convert(char(4),datename(yyyy,'01/01/2008'))+convert(char(2),datepart (wk,'01/01/2008'))
Post #551685
Posted Wednesday, August 13, 2008 5:53 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 37,076, Visits: 31,637
Sara_DBA (8/13/2008)
SELECT CONVERT(VARCHAR,GetDate(),112) :P


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. :P

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

(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 #552363
Posted Wednesday, August 13, 2008 5:55 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 37,076, Visits: 31,637
senthilkumar.v (8/13/2008)
select convert(char(4),datename(yyyy,'01/01/2008'))+convert(char(2),datepart (wk,'01/01/2008'))


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

(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 #552365
Posted Wednesday, August 13, 2008 6:30 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:57 PM
Points: 1,754, Visits: 723
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))))
Post #552376
Posted Wednesday, August 13, 2008 9:57 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 37,076, Visits: 31,637
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."

(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 #552401
Posted Thursday, August 14, 2008 8:40 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:57 PM
Points: 1,754, Visits: 723
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'
Post #552776
Posted Wednesday, January 9, 2013 10:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 10:16 AM
Points: 1,016, Visits: 98
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)
Post #1404902
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse