sorting a date within convert syntax
-
Happy New Year everyone....
ok, here is my situation:
I have code on a page that brings back the last 30 days or last 30 results for glucose labs. Because of the page being loaded by xml, we had to use the convert syntax on the labdate. Now when it sorts(desc), it is putting the '1/1/2006' at the end of all the rest instead of first as being the latest added. I realize that this issue is within the sorting on the converted date, but I don't know how to fix it~! HELP PLEASE. None of my 2006 results are showing and this just isn't good! Thank you!!
-
I'm guessing the problem is that you are converting to VARCHAR and that sorts by dictionary order. It's hard to tell without seeing your results.
Again, without seeing your data, the results you are getting and the results you want, I'm just guessing here..
but try this:
ORDER BY CONVERT(VARCHAR(8), labdate, 112) DESC
-SQLBill
-
Yes, convert works good!
Just yesterday I had to do someting that required date formats not covered by Convert styles and I tried to use Datepart. But because Datepart returns integer it sorts as integer. So I used Convert and then used its substrings to custom-format. This is a more simple example to group/sort by year + month:
Without Style using DatePart:
convert(char(4),DatePart(yy,c.mydate))+'_' + convert(varchar(2),DatePart(mm,c.mydate))
and sorted returns the following (with all ones first even if it is 11)
2005_1
2005_10
2005_11
2005_4
2005_8
With Style parameter:
convert(char(7),c.mydate,121)
and sorted returns the correct sort:
2005-01
2005-04
2005-08
2005-10
2005-11
Bottom Line: Do It With Style 🙂
Regards,Yelena Varsha
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply