MySQL won't be able to guess whether dates like 1-04-06 are 2001-06-04 or
2004-06-01 or ...
This is pretty ugly, but to interpret that I believe you'll need to give
MySQL a hand by telling it which bits go where. Something like,
SELECT
CONCAT('20',RIGHT(datestr,2),'-',LEFT(RIGHT(datestr,5),2),'-',LEFT(datestr,LOCATE('-',datestr)-1))
AS isodate, datestr
FROM dates
ORDER BY isodate DESC ;
This tells MySQL to concatenate the following together into a string which
you can use as a date:
"20",
the rightmost two characters (year),
a hyphen,
the leftmost two chars of the rightmost 5 chars (month)
a hyphen,
the leftmost characters up till the first hyphen (-1 removes the hyphen)
Once you've got that string, you can use MySQL's date_format() to do some
fixery, and update the date column (or a newly added column, if you prefer).
--
create table date_example ( orig_datestr varchar(12), fixed_datestr
varchar(12) ) ;
insert into date_example ( orig_datestr ) values ( '1-04-06' ), ( '11-06-03'
), ( '4-06-08' ), ( '5-06-07' ) ;
update date_example set fixed_datestr =
date_format(concat('20',right(orig_datestr,2),'-',left(right(orig_datestr,5),2),'-',left(orig_datestr,locate('-',orig_datestr)-1)),'%Y-%m-%d')
;
select * from date_example order by fixed_datestr ;
--
PS> I reserve exclusive copyright for discussion of any typos :)
On Wed, Oct 8, 2008 at 2:35 PM, Karl <[EMAIL PROTECTED]> wrote:
>
>
> Thanks Anru... an interesting sounding solution...
>
> How does one go about translating strings within an SQL statement
> please? (Unashamed QueryN00b)
>
> Cheers...
>
>
>
>
> *********** REPLY SEPARATOR ***********
>
> On 7/10/2008 at 6:09 p.m. ctx2002 wrote:
>
> >translate dd-mm-yy to unixtime stamp, then order by that unixtime
> >stamp. you can do it within one SQL statement.
>
>
>
> ---
> Karl
> Senior Account Manager
> www.KIWIreviews.co.nz ... Where Your Views Count
> Please consider the environment before printing this email.
>
> Supporting Palmerston North's Santa... see our Community Gold Project page:
> http://www.KIWIreviews.co.nz/santa - To be seen on TVNZ's 'Mucking In'
> show!
>
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[EMAIL PROTECTED]
-~----------~----~----~----~------~----~------~--~---
