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 |
|
werhardt
Constraint Violating Yak Guru
270 Posts |
 Posted - 2008-02-27 : 08:54:49
|
| When I sort the effective date, it sorts it in month order with all of the years together under that month. Is it possible to sort the effective date in year order? The way it if formated right now is 01-01-2007. Is there any type of formula that I can use?Below is the field that I use.CONVERT(CHAR(10),pcsp.pcsp_eff , 110) |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-02-27 : 08:56:39
|
| I am not sure what you are asking. Can you give an example?Also, you should never be sorting on a CONVERT expression -- that is not only slower, but it sorts dates as STRINGS, not actual datetime values. Just sort simply on the DateTime column itself and it should sort properly.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
werhardt
Constraint Violating Yak Guru
270 Posts |
Posted - 2008-02-27 : 09:26:27
|
| What my client want is this. This how it sorts now by month, 01/01/200702/01/200603/01/2005What my client want is it got be sorted by the year like this below.03/01/200502/01/200601/01/2007Thanks for the heads up on the converted date. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-02-27 : 09:39:50
|
| What is the datatype of the column pcsp_eff?If it is DATETIME, thenOrder by pcsp_eff MadhivananFailing to plan is Planning to fail |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-02-27 : 09:45:33
|
| As I said, don't convert to a CHAR/VARCHAR. Leave your date as is. Never convert data to "make it look right" at the database layer, leave your raw data as is for sorting, comparing, calculations, etc and then simply let your client (reporting tool, web app, Excel, etc) format your data any way it wants.Makes sense?- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
sort by year order, not by month