SQLTeam.com | Weblogs | Forums

Order by decimal


#1

select from table
order by cast(shpdat as int) desc

i am not getting the proper order

I have to order by date below is the example
Shpdat (decimal,9) = 14082010 (DDMMYYYY)


#2

So the dates will be 14082010 for 14-Aug-2010 and 1082010 for the 1st? so that is one digit less "width" and all will sort lower than any 8-digit number

Also, your sort will be by Day then Month then Year ...

Personally we only EVER store dates in a DATE or DATETIME datatype field. Avoids all this sort of issue. We can also use all the Date functions built in to SQL - Adding Days, Months, years etc. or calculating the Difference between two dates (in Days, Months, Years etc.), or finding out what day of the week ... and so on.

You could sort on the right 4 digits of your date, followed by the next two digits (counting from the right) and finally the next one or two digits (again counting from the right, i.e. the left most one or two digits). I think that would be a) easiest and b) fastest as SQL won;t have to do any calculations / manipulations

Or you could cast the decimal number to a Date and then sort on that - BUT if you have any invalid dates that will raise an error.


#3

To get a valid sort order, you have to go from DDMMYYYY to YYYYMMDD:

ORDER BY shpdat % 10000 * 10000 + floor(shpdat / 10000) % 100 * 100 + floor(shpdat / 1000000)


#4

if you are able to change the table design, change the data type for column shpdat to date or datetime

then you can just simply ORDER BY shpdat DESC