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 |
|
daviddeldave
Starting Member
28 Posts |
 Posted - 2001-12-20 : 05:08:00
|
Not a contradiction! I have data stored as CHAR(5) but is actually meant to be dates and because of this I am having problems getting the sorting in date order instead of text order. The data is in the for of:APR01MAR01JUN01JUL01AUG01SEP01OCT01 ETCWHEN I USE CONVERT(DATETIME,'APR01') FOR EXAMPLE I GET "Syntax error converting datetime from character string."I think the problem is that the data relates to monthly dates as opposed to d/m/y values. Any ideas appreciated.Thanks David  David Mercer |
|
|
sica
Posting Yak Master
143 Posts |
Posted - 2001-12-20 : 05:52:41
|
| You could try a case like this:select * from authors order by case when state = 'CA' then 1 when state = 'OR' then 2 else 3 endYou have 12 month så it can be too hard to write it...Sica |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-20 : 07:11:36
|
| You'll need to give it a particular date in the month, but 3 character date names like that should be convertable. Try this:CONVERT(datetime, '1' + monthlyDate) |
|
|
|
nricardo
Starting Member
17 Posts |
Posted - 2001-12-20 : 11:11:51
|
| When storing month and year data without a day, I recommend using an integer. The month and year should be stored as yyyymm. For example December 2001 would be 200112. This will always sort correctly. |
|
|
|
|
|
|
Sorting Text Data as Date Data