Formatting...date output

Question

Post reply

Formatting...date output

b_boy

SSCertifiable

Points: 5145
More actions
September 24, 2008 at 8:34 am

#198709

Hello
Am using the following query to determine a fiscal week. The result output is fine and i get '200843'; but i will like to format the date output to '2008-43', does anyone know how?
drop function FiscalWeek
go
create function FiscalWeek (@startMonth varchar(2), @myDate datetime)
returns int
as
begin
declare @firstWeek datetime
declare @weekNum int
declare @year int
set @year = datepart(year, @myDate)+1
--Get 4th day of month of next year, this will always be in week 1
set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102)
--Retreat to beginning of week
set @firstWeek = dateadd(day, (-datepart(dw, @firstWeek)), @firstWeek)
while @myDate < @firstWeek --Repeat the above steps but for previous year
begin
set @year = @year - 1
set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102)
set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)
end
set @weekNum = (@year*100)+((datediff(day, @firstweek, @myDate)/7)+2)
return @weekNum
end

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You are using all numeric types to you won't get the result you want as 2008-43 is a character type, not a numeric.

I'd change @WeekNum to char(7) and then when setting @WeekNum I'd do this:

set @weekNum = Convert(Char(4), (@year*100)) + '-' + Convert(char(2), ((datediff(day, @firstweek, @myDate)/7)+2))

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

You will need to cast your return value as CHAR (or VARCHAR) if you want an embedded non-numeric character:

[font="Courier New"]DECLARE @weekNum INT, @WeekName CHAR(7)

SET @weekNum = 200843

SET @WeekName = STUFF(CAST(@weekNum AS VARCHAR(7)), 5, 0, '-')

SELECT @weekNum, @WeekName

[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

b_boy SSCertifiable Points: 5145 More actions · Answer 3

Hello Jack

Got the following error msg:

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value '*-43' to data type int.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Sorry, I was trying to figure out why you had the * 100 and now I know. You need to remove the *100. The code should be:

set @weekNum = Convert(Char(4), @year) + '-' + Convert(char(2), ((datediff(day, @firstweek, @myDate)/7)+2))/code]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

b_boy SSCertifiable Points: 5145 More actions · Answer 5

Hello Jack

the year and week together (multiply year by 100 to make room for the week number and add it on) We include the year so that if the result set is more than a year in scope say 2004 - 2006 , week 5 for 2004 won't get grouped with week 5 in 2005 and 2006. And the week number is easily separated using modulus (example 200405 % 100 = 5 )

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

Stuff it...:P seriously... the STUFF solution that Chris Morris posted will do the trick just fine.

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