Sorting by date when the field is type varchar

[deepatpaul]

I have a query that sorts results with various fields being used in the ORDER BY clause, and the last one of four is the date field. However, the dates are being stored in varchar fields (SQL Server), and I fear that CF is not outputting the dates properly since it's sorting by checking the values of the date from left-to-right, as opposed to looking at the actual date segments.

If this is the case, is there a way I can have CF look at the varchar-based dates and have them sorted based on the value of the date? Or do I have to convert the field to datetime type?

 

[r937]

why would you want cf to do it? by that time, it's too late

do the sort in the query

order 
    by column1
     , column2
     , column3
     -- assuming varchardate is mm/dd/yyyy     
     , substring(varchardate,7,4) -- yyyy 
     , substring(varchardate,1,2) -- mm  
     , substring(varchardate,4,2) -- dd

and yes, it would be better to convert to datetime

that way you can do date arithmetic too, like finding all the dates within the last 30 days and stuff


rudy

http://r937.com/

SQL Consulting

 

[deepatpaul]

I didn't mean for CF, per se, to sort it. My fault for lack of proper wording. Anyway, thank you for this. I'll give it a run-through.