On Mon, 05 May 2008 13:28:33 +0200, Paul Lautman
<paul.lautman@btinternet.com> wrote:

> 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:

Damn, I was looking for it in the date/time functions in the manual, but
apparantly totally overlooked it. I nice sign all my dates/datetimes I had
the pleasure of visiting in other applications lately were all actually
datetimes or unix timestamps

Noted for the moment I run into a crap database/table/field again, let's
hope it will take a while.
--
Rik Wasmus