Week Order

3 Mar 2006 7:41 AM

niel

We have a SQL statement that has the following statement.

DATENAME(YEAR,"dbo"."Deal"."TradeDate"-2)+' Week
'+DATENAME(WEEK,"dbo"."Deal"."TradeDate"-2)

The problem with this is that it shows the data as follows.

2005 Week 19
2005 Week 2
2005 Week 20

We want though

2005 Week 2 .... or 2005 Week 02
2005 Week 19
2005 Week 20

What is the correct SQL statement to do this?

Thanks for any help

3 Mar 2006 7:46 AM

oj

DATENAME(YEAR,"dbo"."Deal"."TradeDate"-2)+' Week
'+right('00'+DATENAME(WEEK,"dbo"."Deal"."TradeDate"-2),2)

--
-oj

Show quote

"niel" <n***@fabs.com> wrote in message
news:%232DfFVpPGHA.2080@TK2MSFTNGP09.phx.gbl...
> We have a SQL statement that has the following statement.
>
> DATENAME(YEAR,"dbo"."Deal"."TradeDate"-2)+' Week
> '+DATENAME(WEEK,"dbo"."Deal"."TradeDate"-2)
>
> The problem with this is that it shows the data as follows.
>
> 2005 Week 19
> 2005 Week 2
> 2005 Week 20
>
> We want though
>
> 2005 Week 2 .... or 2005 Week 02
> 2005 Week 19
> 2005 Week 20
>
> What is the correct SQL statement to do this?
>
> Thanks for any help
>

3 Mar 2006 8:00 AM

Jens

If you are talking about the order, then simply use at the end of your
statement:

ORDER BY DATENAME(YEAR,"dbo"."Deal"."TradeDate"-2),
DATENAME(WEEK,"dbo"."Deal"."TradeDate"-2)

HTH; Jens Suessmeyer.

4 Mar 2006 8:56 AM

Tibor Karaszi

In addition to the other posts:

Don't use DATENAME or DATEPART if your culture assumes (as the ISO standard does) that jan 1 of 2006
is week number 52. Try below:

SET DATEFIRST 1
SELECT DATENAME(WEEK, '2005-01-01')

If you want above to return 52 then install the ISOWEEK function found in Books Online or use a
calendar table.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"niel" <n***@fabs.com> wrote in message news:%232DfFVpPGHA.2080@TK2MSFTNGP09.phx.gbl...
> We have a SQL statement that has the following statement.
>
> DATENAME(YEAR,"dbo"."Deal"."TradeDate"-2)+' Week '+DATENAME(WEEK,"dbo"."Deal"."TradeDate"-2)
>
> The problem with this is that it shows the data as follows.
>
> 2005 Week 19
> 2005 Week 2
> 2005 Week 20
>
> We want though
>
> 2005 Week 2 .... or 2005 Week 02
> 2005 Week 19
> 2005 Week 20
>
> What is the correct SQL statement to do this?
>
> Thanks for any help
>