Sandy80 wrote:
> Hi,
>
> The table that I have has a sample records like below:
>
> Emp No. Unique ID From Date To Date
> 11 1234 01-Jan-2005 30-Sep-2005
> 11 2345 01-Oct-2005 31-Oct-2005
> 11 3456 01-Nov-2005 31-Oct-2005
>
>
> The task that I have is to find out records where the From Date is
> greater than the To Date. This is needed because the 3rd record in the
> example above is actually an erroneous record which needs to be
> deleted. The query that I was able to write was returning the 2nd
> record also because 01-Oct-2005 is greater than 30-Sep-2005. Please
> help me with the query which would return only the 3rd record.
>
> Any help would be appreciated...thanks!
> Sandy

Whilst I totally agree with Rik's sentiment on the design of the table (if
you let an idiot design the database and not use a date datatype for date
fields, you can not expect to be able to actually work with the data later),
you do not actually need to use an external script or a bunch of REPLACE and
CONCAT statements.

Assuming that (as Rik stated) your data is reliable (i.e. it is all in that
crap date format), you can use the STR_TO_DATE function thus:

SELECT
*
FROM crap_table
WHERE STR_TO_DATE(from_date, '%d-%b-%Y' ) > STR_TO_DATE(to_date,
'%d-%b-%Y' )

However, I would strongly suggest you use the INSERT ... SELECT statement to
convert the data into something that should be used in a database (using the
STR_TO_DATE function)