Order by a date related field

[justride]

Hi,

This may sound awkward, but I have a field that is of varchar(10) type with dates in the format mm/dd/yyyy
Is there anyway to query on this table and sort by this field although its not of date type? I want to sort asc and desc but I am guessing I need to cast on the fly or something? I'd like to use this convention going forward (I know its stupid) It was done this way as an easy way out.

Thanks,
Chris

 

[r937]

an easy out"

yes, you have to break it up on the fly and reassemble it in yyyy, mm, dd sequence

fortunately, there is the SUBSTRING_INDEX function which makes this a snap

r937.com | rudy.ca

 

[ericbrunson]

There's never time to do it right, no matter how much time it takes to deal with it later. ;-)

 

[justride]

Hi, Thanks for the suggestion:

I used the SUBSTRING_INDEX

SELECT APPID,
CONCAT(SUBSTRING_INDEX(`DATE`, '/', 1),'-', 
SUBSTRING_INDEX(SUBSTRING_INDEX(`DATE`, '/', -2), '/', 1),'-',
SUBSTRING_INDEX(SUBSTRING_INDEX(`DATE`, '/', -1), '/', 1)) AS NEWDATE
FROM LOANLOG
ORDER BY NEWDATE  ASC

which returns a column of dates in the format of mm-dd-yyyy instead of mm/dd/yyyy. there is even probably an easier way to do that by like string substitution, but anyway, how can I sort these by date asc or desc? Its not working right now and thats because the type is varchar, do i need to cast to a date field?

Thanks

 

[r937]

no, you do not need to cast as a date field, because character strings for yyyy, mm, dd will sort into the exact same sequence, which is all that you're after


select APPID
from LOANLOG
order
by substring_index(`DATE`,'/',-1)
, right(concat('0',
substring_index(`DATE`,'/',1)),2)
, right(concat('0',
substring_index(
substring_index(`DATE`,'/',2)
,'/',-1)),2)



r937.com | rudy.ca

 

[justride]

Ok, I have it selecting colums in the format of yyyy-mm-dd

SELECT APPID,
CONCAT(
SUBSTRING_INDEX(SUBSTRING_INDEX(`DATE`, '/', -1), '/', 1),'-',
SUBSTRING_INDEX(`DATE`, '/', 1),'-',
SUBSTRING_INDEX(SUBSTRING_INDEX(`DATE`, '/', -2), '/', 1)) AS DATE
FROM LOANLOG
ORDER BY APPID DESC

The problem now is there are some leading zeros here and there that are screwing up the sorts. i need to take out leading zeros or add them into the results.

any suggestions?

for example i have
2005-1-23
2005-04-03
2005-03-12

etc...

 

[r937]

did you try my suggestion? it handles the 0s

r937.com | rudy.ca

 

[justride]

CASE CLOSED, I RESOLVED THE LEADING ZERO ISSUE

THANKS TO ALL

SELECT APPID,
CONCAT(
trim(leading '0' from (SUBSTRING_INDEX(SUBSTRING_INDEX(`DATE`, '/', -1), '/', 1))),'-',
trim(leading '0' from (SUBSTRING_INDEX(`DATE`, '/', 1))),'-',
trim(leading '0' from (SUBSTRING_INDEX(SUBSTRING_INDEX(`DATE`, '/', -2), '/', 1)))) AS DATE
FROM LOANLOG
ORDER BY APPID DESC

 

[r937]

wait till you try to sort dates that span an entire year --

2005-1-15
2005-10-15
2005-11-15
2005-12-15
2005-2-15
etc.

you shouldn'ta stripped those zeros, man!



r937.com | rudy.ca

 

[justride]

hey all, i am a bonehead...

i need to add zeros if they dont exist, any suggestions?

 

[justride]

im redoing my schema, screw my old crappy way