SQL Year and Week numbers

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » SQL Year and Week numbers

20 posts, Page 2 of 2

««1 2

SQL Year and Week numbers

Rate Topic

Display Mode

Topic Options

Author

Message

jennyor

Posted Tuesday, August 12, 2008 11:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 03, 2009 6:25 AM
Points: 2, Visits: 15

Jeff, It worked like a charm thanks!
Jenny

Post #551243

Jeff Moden

Posted Tuesday, August 12, 2008 6:12 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #551503

Sara_DBA-629440

Posted Wednesday, August 13, 2008 12:50 AM

Grasshopper

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

senthilkumar.v

Posted Wednesday, August 13, 2008 2:52 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, March 25, 2013 2:12 AM
Points: 428, Visits: 108

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

Post #551685

Jeff Moden

Posted Wednesday, August 13, 2008 5:53 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789

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

Post #552363

Jeff Moden

Posted Wednesday, August 13, 2008 5:55 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789

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

Post #552365

Mike Mullen

Posted Wednesday, August 13, 2008 6:30 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 1:28 PM
Points: 1,411, Visits: 675

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

Jeff Moden

Posted Wednesday, August 13, 2008 9:57 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789

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

--Jeff Moden

Post #552401

Mike Mullen

Posted Thursday, August 14, 2008 8:40 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 1:28 PM
Points: 1,411, Visits: 675

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

arthur.teter

Posted Wednesday, January 09, 2013 10:58 AM

Right there with Babe

Group: General Forum Members
Last Login: Monday, April 29, 2013 11:52 AM
Points: 751, Visits: 76

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 »

20 posts, Page 2 of 2

««1 2

Permissions