ordering date in varchar

------=_Part_10350_14975279.1205397340001
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

We have a problem. We have given the dates as date.month.year eg 27.12.2007
The field is named as varchar. We have to arrange ths list of dates in
ascending order. Can anyone help? Varchar arranges the list by scanning from
left side. Perhaps scanning from right side will do. How to do it?


--
Vaibhav Informatics, 109 Vasant Plaza, Munirka, Near Subway, New Delhi-110
067 Ph: 011-26194466 Email :vaibhavinformatics@gmail.com

------=_Part_10350_14975279.1205397340001--

RE: ordering date in varchar

I think you can try the SQL:
Select STR_TO_DATE([field_name], '%d/%m/%Y') as newDate , [field_name] =
from [table] order by newDate asc;

Thanks,
Haitao


-----Original Message-----
From: Vaibhav Informatics [mailto:vaibhavinformatics@gmail.com]=20
Sent: 2008Äê3ÔÂ13ÈÕ 16:36
To: php-db@lists.php.net; =
php-db-digest-sc.1196674013.gdgkepajakejmghdjkad-vaibhavinfo rmatics=3Dgma=
il.com@lists.php.net; php-db-digest-help@lists.php.net
Subject: [PHP-DB] ordering date in varchar

We have a problem. We have given the dates as date.month.year eg =
27.12.2007
The field is named as varchar. We have to arrange ths list of dates in
ascending order. Can anyone help? Varchar arranges the list by scanning =
from
left side. Perhaps scanning from right side will do. How to do it?


--=20
Vaibhav Informatics, 109 Vasant Plaza, Munirka, Near Subway, New =
Delhi-110
067 Ph: 011-26194466 Email :vaibhavinformatics@gmail.com

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: ordering date in varchar

> I think you can try the SQL:
> Select STR_TO_DATE([field_name], '%d/%m/%Y') as newDate , [field_name] from [table] order by newDate asc;
>
> Thanks,
> Haitao
>
>
> -----Original Message-----
> From: Vaibhav Informatics [mailto:vaibhavinformatics@gmail.com]
> Sent: 2008Äê3ÔÂ13ÈÕ 16:36
> To: php-db@lists.php.net; php-db-digest-sc.1196674013.gdgkepajakejmghdjkad-vaibhavinfo rmatics=gmail.com@lists.php.net; php-db-digest-help@lists.php.net
> Subject: [PHP-DB] ordering date in varchar
>
> We have a problem. We have given the dates as date.month.year eg 27.12.2007
> The field is named as varchar. We have to arrange ths list of dates in
> ascending order. Can anyone help? Varchar arranges the list by scanning from
> left side. Perhaps scanning from right side will do. How to do it?
>
>
For the best functionality and performance you should use date fields
for your database.

--
Isaak Malik
Web Developer
isooik@gmail.com

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: ordering date in varchar

>Message-ID: <37f3cbf50803130135m799582a5j33929e750c41d298@mail.gmail.com>
>Date: Thu, 13 Mar 2008 14:05:39 +0530
>From: "Vaibhav Informatics"
>
>We have a problem. We have given the dates as date.month.year eg 27.12.2007
>The field is named as varchar. We have to arrange ths list of dates in
>ascending order. Can anyone help? Varchar arranges the list by scanning from
>left side. Perhaps scanning from right side will do. How to do it?


OK I'll take this one too then ;-)

You're right that ORDER BY will treat that as a string, so the
ordering will be broken.
There are 2 options I can think of to handle this without changing
the table structure :

One is to split up the string, then re-join it and ORDER BY that as a
calculated column,
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html #function_concat
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html #function_substring


SELECT CONCAT(
SUBSTRING(datefield, 7, 4),
SUBSTRING(datefield, 4, 2),
SUBSTRING(datefield, 1, 2)
) AS dateresult FROM yourdatabase
..
..
..
ORDER BY dateresult ASC


The other is to cast it to a DATE type using STR_TO_DATE, then order by that :
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#function_str-to-date


SELECT STR_TO_DATE(`datefield`, '%d.%m.%Y') AS dateresult FROM test
..
..
..
ORDER BY dateresult ASC



TBH though, you have a big headache here because none of that is
going to be amenable to indexing.

If you end up with several thousand rows after the WHERE statement
(eg you select all rows) then the performance is going to be
dreadful. MySQL will have to build a temporary table to hold the
ordering - it'll do it in memory at first, then to disk if the
resultset is too large (which is *really* bad for performance).

Your best option if you have any control over the table schema at
all, is to LOCK TABLES to avoid unintended changes, then create a new
column perhaps using STR_TO_DATE as above to populate a proper MySQL
DATE type column.

Modify your application (PHP) script to do the strung
munging/rearranging on input, so that's a job you only have to do once.

Now you should add an index to the DATE column to get reasonable
performance if this is a highly trafficked table in which you intend
to order by the date field a lot.


HTH
Cheers - Neil



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php