January 2, 2010 at 7:16 am
I am getting the date column like 12/1/2009
and 12/12/2009 in descending order. upto 12/12/2009 there is no problem. But after 2010 i am not getting latest date first. I have to get the report as like
1/1/2010
12/12/2009
12/11/2009
.
.
.
12/1/2009
Can anyone help me how to get the date column as i mentioned above?
January 2, 2010 at 7:59 am
you need to provide more information. If the dates are stored in datetime format the sorting will working properly, it appears that you are trying to sort on an alpha field. Provide the structure of the table and the query and you will get a response back as to why your sort is not working properly
January 2, 2010 at 9:00 am
As jcdyntek noted, if you are getting this sorting, likely you have character storage.
"1" comes before "12" in terms of character values, and "1/10/2010" comes before "1/2/2010" as well. 1 before 2, 2 before 3, shorter before longer, in terms of character sorting.
January 2, 2010 at 9:08 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 11, 2010 at 6:32 am
I know that I had a similar problem with some of my reports... I would convert the date to a varchar to eliminate the time component, i.e., CONVERT(varchar,InvoiceDate,101) as InvoiceDate to automatically get groupings by day... but then the order was incorrect due to 1 being larger than 0, i.e.,
12/30/2009
12/30/2009
12/24/2009
12/22/2009
01/08/2010
01/05/2010
01/05/2010
01/04/2010
I changed my ORDER BY to convert the value back to datetime,
ORDER BY CAST(convert(varchar,InvoiceDate,101) as smalldatetime) DESC
This did the trick.
As mentioned before, please provide more details, examples, etc. to help diagnose and provide suggestions!
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
January 11, 2010 at 7:31 am
You really dont need to do that. You can format the date anyway you like but still keep the order by on the actual column invoice date and you can be assured the sort will always be correct.
January 11, 2010 at 7:42 am
Unfortunately I do have to do all of those contortions as I am using CONVERT(varchar,InvoiceDate,101) in the SELECT statement and the GROUP BY. If I just try and use just InvoiceDate in the ORDER BY I get a
Column "InvoiceDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
error message.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
January 11, 2010 at 7:51 am
Not that I am trying to take over someone's elses post, but here was the example query I was using:
SELECT CONVERT(varchar,Invoicedate,101) as PostedDate ----- Group by date as there can be multiple invoices on same day different times
,CustomerNumber, CustomerName
,sum(InvoiceAmt) as InvoiceTotals
,count(InvoiceNo) as CntInvoices
from InvoiceData
where InvoiceDate between '12/1/09' and DATEADD(d,1,'1/11/10')
GROUP by CONVERT(varchar,InvoiceDate,101), CustomerNumber, CustomerName
ORDER BY CONVERT(vachar,InvoiceDate,101) DESC
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply