Finding out erroneous records

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

On Mon, 05 May 2008 10:01:43 +0200, Sandy80 <svarshneymail@gmail.com>
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

Having real date columns would have saved you a lot of headaches...
This will simply not do:
mysql> select date('01-Oct-2007');
+---------------------+
| date('01-Oct-2007') |
+---------------------+
| NULL |
+---------------------+
1 row in set, 1 warning (0.00 sec)

And compare 'from date' to 'to date' will just do a string comparison
instead of time. I suggest using any script you are familiar with to alter
the dates to something mysql is comfortable with (yyyy-mm-yy), and
changing the columns to date columns. Offcourse, if your data is reliable,
you could run 12 REPLACE()s to get the dates to a numeric format, and
shuffle the values around with CONCAT() & SUBSTR().

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

After you've done that, its a simple matter of:
SELECT `Unique ID` FROM tablename WHERE `From Date` < `To Date`;


Altering the database/table in this way may be a lot of work at first,
depending how and how much other code uses it, but will have major
benefits in the end.
--
Rik Wasmus

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)

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