Sort By Date??
-
Hi i am using this sql statement on (SQL Server 2000) and tryin to sort the results on tl.Date but as i have already casted it to a nvarchar it sorts it like a char so im just wondering if anyone has any idea how to sort it by date correctly. thanks in advance Tim
select SUBSTRING(CAST(tl.Date as nvarchar), 0, 12) as 'Date', tft.BodyWeight from Person as p left join TrainingLog tl on tl.PersonId = p.Id left join TrainingFitnessTesting tft on tl.Id = tft.TrainingLogId where p.Id = 8 and tl.Date >= '01 January 2005' and tl.Date <= '03 January 2006' and tft.BodyWeight IS NOT NULL order by tl.Date asc -
Hi i am using this sql statement on (SQL Server 2000) and tryin to sort the results on tl.Date but as i have already casted it to a nvarchar it sorts it like a char so im just wondering if anyone has any idea how to sort it by date correctly. thanks in advance Tim
select SUBSTRING(CAST(tl.Date as nvarchar), 0, 12) as 'Date', tft.BodyWeight from Person as p left join TrainingLog tl on tl.PersonId = p.Id left join TrainingFitnessTesting tft on tl.Id = tft.TrainingLogId where p.Id = 8 and tl.Date >= '01 January 2005' and tl.Date <= '03 January 2006' and tft.BodyWeight IS NOT NULL order by tl.Date ascAs per my knowledge this query will work fine and will display the result sorted based on tl.Date as Date. I may be mis-understanding your question but I think as you have converted Date into nvarchar in select clause not in Order By clause, hence it will have no effect on the sorting. Akif
-
As per my knowledge this query will work fine and will display the result sorted based on tl.Date as Date. I may be mis-understanding your question but I think as you have converted Date into nvarchar in select clause not in Order By clause, hence it will have no effect on the sorting. Akif
someone has actually solved the problem now but just in case u would like to know when i got a substring of the date i got dates like Dec 12 2005 Sept 23 2005 sorted incorrectly as sept should be before dec but because they are nvarchar they are sorted as characters as in D comes before S in the alphabet hopes this clears it up thanks anyway Tim