Convert varchar to date and sort 2

[redtoad]

I am trying to return a varchar converted to date. Then I want to sort by the date. Returned values vary, some are 02/01/2004, some 2/01/2004, etc. This is causing sql to not sort the way I want. I would like to sort chronologically by year, then month, then day.

Here's my query

Select Convert(Varchar(15), FieldValue, 101)
from data 
where fieldID = 289
order by fieldvalue asc

Here's a typical result set.
02/13/2002
1/1/1900
1/1/2000
1/10/1999
1/12/2003
1/4/1999
1/8/2003
10/14/2002
10/30/2002
10/31/2002
12/24/1998
2/26/1998
3/16/2004
8/7/2002 1:49:3
8/9/2002
9/10/1999
9/2/1998
9/22/1998
9/23/2002
9/3/1998
9/5/2003

 

[donutman]

I think you mean that FieldValue is a varchar to start with. So just Cast(FieldValue as datetime) and sort by the exact same expression. Select whatever you want.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[john76]

Here's an example of donutman's idea:

SELECT  CONVERT(varchar, FieldValue, 101)
  FROM  MyTable
  WHERE FieldID = 289
  ORDER BY CONVERT(varchar, FieldValue)

HTH,
John

 

[redtoad]

I was able to acheive the desired result by:

Select fieldvalue as datetime
from data 
where fieldID = 289
order by Cast(fieldvalue as datetime)

Thanks for the help. I guess I was making it too complicated.

 

[SQLSister]

Is there some reason why this isn't stored as datetime data? Having to continually convert is not very efficient. Plus you are subject to the idiots who will store non-date values inthe field or ones which are not correct dates (2/31/2004). Your code may have to account for this or it will break when someone does do this as it the data wil not be convertable to datetime. Better to change the datatype if that is possible and avoid the whole problem.

Questions about posting. See faq183-874

 

[john76]

Plus you are subject to the idiots who will store non-date values inthe field or ones which are not correct dates (2/31/2004).

The production database (and I use that term loosely) at my job has all date values broken into Month, Day, [Century], and Year fields. Because of this idiot method, I actually have a February 31st date in the system. Not to mention a 8200 year, several 2100 and 2029 years.

Oh well, keeps the paychecks rolling in.

John

 

[donutman]

lol

 

[donutman]

BTW, redtoad, I was really confused by the
Select fieldvalue as datetime
but then I'm kinda dull sometimes. It would less confusing if the alias wasn't the name of a data type.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[redtoad]

I made a mistake on my post, should have been Select Cast(fieldvalue as datetime).

As for the date being varchar...this is an issue that comes up time and time again. We have to account for it in much of our code, but there are always places that have been missed. I don't know that it will ever be addressed from the db point of view.