need help with formating date and sorting it descending 1

[DougP]

My column Week Ending Date is Datetime

Select ResourceLastName, ResourceFirstName, Manager,
convert(nvarchar(12),CAST(WeekEndDate as Date),101) as WeekEndDate, ApprovedBy
from dbo.SOWTimeReporting Where ApprovedBy = 'NotYetApproved'
Order by WeekEndDate desc

I am using the above convert... to make it into 09/15/2012. but when I sort it descending I get dates of 09/08/2012 first, since its a character, so it puts September last year first.
I want May of this year 05/21/2013 first and September last year, last.

DougP

 

[imex]

Try:

Select 
    ResourceLastName, 
    ResourceFirstName, 
    Manager,
    DATEADD(DAY, DATEDIFF(DAY, 0, WeekEndDate), 0) as WeekEndDate, 
    ApprovedBy
from dbo.SOWTimeReporting 
Where ApprovedBy = 'NotYetApproved'
Order by WeekEndDate desc

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[bborissov]

Don't convert it to chars:

Select ResourceLastName,
       ResourceFirstName,
       Manager,
       CAST(WeekEndDate as Date) as WeekEndDate,
       ApprovedBy
from dbo.SOWTimeReporting Where ApprovedBy = 'NotYetApproved'
Order by CAST(WeekEndDate as Date) DESC

Borislav Borissov
VFP9 SP2, SQL Server

 

[gmmastros]

try this order by clause:

Order by CAST(dbo.SOWTimeReporting.WeekEndDate as Date) desc

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

Sweet gmmastros!
simple quick, love it, have a star

DougP

 

[gmmastros]

What's interesting here is that the ORDER BY clause is the ONLY sql clause that can use column aliases. Since your table has a column named WeekEndDate and you have column in your SELECT clause aliased at WeekEndDate, the Order by uses the aliased version from your select clause.

Take a look at the following example:

Declare @Temp Table (SomeDate DateTime)

Insert Into @Temp Values('20131001')
Insert Into @Temp Values('20121101')
Insert Into @Temp Values('20131201')

-- Ordering based on select clause alias
Select Convert(nvarchar(12), SomeDate, 101) As SomeDate
From   @Temp T
Order By SomeDate

-- Ordering based on table data
Select Convert(nvarchar(12), SomeDate, 101) As SomeDate
From   @Temp T
Order By [!]T.[/!]SomeDate

Notice that the date from November is 2012 while the rest are 2013. When you run the query above you get different results even though the only difference in the query is in the order by clause. The first one orders based on month first. The second orders it by the actual date.

I only mention this because it can lead to hard to find bugs in the ordering.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom