Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-04 : 15:12:16
|
| Jason writes "Field:filename, text, 255 characters maxexample filenames:IAR_9804RT1234_description.docIAR_9903RT4312_description2.docIAR_0007CT4321_description3.docIAR_0212RT3321_description4.docIAR_YYMMXXXXXX_XXXXXXXXXXXX.docwhere YY and MM are years and monthsIn an sql query, I wish to sort these by their years, either ascending or descendingYou can't use ORDER BY filename ASC; because '00' is considered smaller than '98'. (The computer doesn't know that '00' means 2000 and '98' means 1998)." |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-04 : 15:30:16
|
| ORDER BY (5+SUBSTRING(filename,5,1))%10, filenamesorry, I'm in a strange sort of mood. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-04 : 15:44:55
|
| This should work too:ORDER BY Convert(datetime, SubString(FileName, 5, 4) + '01', 12), Right(FileName, Len(FileName)-9) |
|
|
|
jkealey
Starting Member
6 Posts |
Posted - 2001-12-04 : 15:50:57
|
quote:
ORDER BY (5+SUBSTRING(filename,5,1))%10, filenamesorry, I'm in a strange sort of mood.
this would limit the number of years too much, no?Edited by - jkealey on 12/04/2001 15:52:27 |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-04 : 15:57:20
|
| Limit too much to what? You didn't say when you actually wanted it to change, so I wildly assumed 1950-2049. Seemed pretty reasonable to me. |
|
|
|
|
|
|
|
|
Tough question