force 2 digit week numbers 2

[jonhowe]

Any ideas on a more elegant way to generate a 2 digit week number i.e. in the range 01-54 than:

select REPLICATE('0',2-len(DATENAME(ww,datefield))) + DATENAME(ww,datefield)

I must have 01, 02 etc not 1,2 etc for weeks < 10.

 

[hspoulsen]

No, but I would love to hear about one.

 

[vongrunt]

Slightly more elegant:

select right( '00' + datename(ww, datefield), 2 )

 

[dbomrrsm]

Very neat Vongrunt

[blue]DBomrrsm[/blue]

 

[hspoulsen]

yes, that looks better, and I could turn it into a UDF, so that it became
select dbo.myweek(datefield) ...

 

[donutman]

Don't turn it into a UDF unless you don't mind a performance penalty of about 20%.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]