forming a string with dateparts

[rgintexas]

I want to create a string using the week number and year of a date.
For instance, if i have a date of 1/3/2007, which would be week 1 of
2007, i want to create a string that says 'Wk1-200707'.
i know it's basically 'WK' + CAST(DATEPART(WK,'1/3/2007') AS VARCHAR)
+ '-' +CAST(DATEPART(YEAR,'1/3/2007') AS VARCHAR) but it's not quite
working for me. Any idea of what I'm missing?
Thanks in advance.
Richard Garth

[Piero 'Giops' Giorgi]

On Aug 30, 8:31 am, rgintexas <[e-mail]:

I want to create a string using the week number and year of a date.
For instance, if i have a date of 1/3/2007, which would be week 1 of
2007, i want to create a string that says 'Wk1-200707'.
i know it's basically 'WK' + CAST(DATEPART(WK,'1/3/2007') AS VARCHAR)
+ '-' +CAST(DATEPART(YEAR,'1/3/2007') AS VARCHAR) but it's not quite
working for me. Any idea of what I'm missing?

Besides using the date in the YYYYMMDD format, no.
Works perfectly for me... What's the problem?

P

[Dan Guzman]

You might get different week values than you expect if DATEFORMAT is set to
DMY instead of MDY. As Piero alluded, specifying date literals in the ISO
YYYYMMDD format will ensure the date is interpreted correctly regardless of
DATEFORMAT settings.

Another possibility is that DATEFIRST is set to an unusual value like 2 or 3
(Tuesday or Wednesday) instead of a common value like 7 or 1 (Sunday or
Monday).

--
Hope this helps.

Dan Guzman
SQL Server MVP

"rgintexas" <rg*******@yahoo.comwrote in message
news:11**********************@y42g2000hsy.googlegr oups.com...

>I want to create a string using the week number and year of a date.
For instance, if i have a date of 1/3/2007, which would be week 1 of
2007, i want to create a string that says 'Wk1-200707'.
i know it's basically 'WK' + CAST(DATEPART(WK,'1/3/2007') AS VARCHAR)
+ '-' +CAST(DATEPART(YEAR,'1/3/2007') AS VARCHAR) but it's not quite
working for me. Any idea of what I'm missing?
Thanks in advance.
Richard Garth