select between returns funny results

[Ratzie]

I have data that is a char field but holds dates.
It looks like this.
20060428AprFri095748095748AM
20060428AprFri103723103723AM
20060428AprFri104758104758AM
20060428AprFri162216042216PM
20060430AprSun010335010335AM
20060501MayMon101308101308AM
20060501MayMon121534121534PM
20060501MayMon134633014633PM
20060501MayMon145649025649PM
20060501MayMon173210053210PM
20060502MayTue145323025323PM
20060502MayTue145831025831PM
20060502MayTue150607030607PM
20060502MayTue151447031447PM
20060502MayTue155103035103PM
yada...

I want to run a select for: between 20060430 and 20060501
But I need to get more detailed in the time stamp, like 000000 and 235959
As you can see, it has the time stamp to show:
YYYYMMDDMonDay then
HHMMSI24HHMMSIAM
So the same time is displayed in 22hr and 12AM/PM

I can run this query:
SELECT
COMPLETED_DT
FROM TABLE
WHERE TABLE.COMPLETED_DT BETWEEN '20060428' AND '20060504'

Still returned results, when I would have thought I would need a wild card.

I tried using the ' - ' So I would queury on 20060428------16% but I still got everything.

This is on an Oracle 7 database

[Indira Aramandla · Accepted solution]

Hi LHradowy,

You can select the COMPLETED_DT from the table with the comparison operator 'like' with the pattern match character '_' or '%'.

The '_' in the pattern match matches exactly one character, and '%' can match zero or more characters except null.

Here are few examples that I tried with your data.

Select the records that has COMPLETED_DT between '20060430' and '20060501'. As you can see there is only one record. If you need 20060501 included then give bweteen '20060430' and '20060502'

SQL> select COMPLETED_DT from table where COMPLETED_DT between '20060430' and '20060501';

COMPLETED_DT
----------------------------------------
20060430AprSun010335010335AM


Select the records that has COMPLETED_DT starts from 20060428, i.e that date and any time for that date. As you can see your data has 3 records with Date 20060428 and different times. Here is the output.

SQL> select COMPLETED_DT from table where COMPLETED_DT like '20060428%';

COMPLETED_DT
----------------------------------------
20060428AprFri103723103723AM
20060428AprFri104758104758AM
20060428AprFri162216042216PM


Select the records that has COMPLETED_DT between â 20060430â and â 20060501â in a formatted way like date and time components

SQL> select substr(cdt,1,14)||' '||substr(cdt,21,8) from indira where cdt between '20060430%' and '20060501%';

SUBSTR(COMPLETED_DT
------------------------
20060430AprSun 010335AM


Select the records that has COMPLETED_DT between â 20060430â and â 20060501â in a more formatted way to get the date, Month, Week-day and time separately.


SQL> select substr(COMPLETED_DT,1,8)||' '||substr(COMPLETED_DT,9,3)||' '||
substr(COMPLETED_DT,12,3)||' '||substr(COMPLETED_DT,21,2)||':'||
substr(COMPLETED_DT,23,2)||':'||substr(COMPLETED_DT,25,2)
from table where COMPLETED_DT between '20060430%' and '20060501%'


SUBSTR(COMPLETED_DT
--------------------------
20060430 Apr Sun 01:03:35


I hope this helps

Indira A

[Hein van den Heuvel]

Hi there,

Kindly re-read your question.
It is not entirely clear what you are trying to select.
At the very least indicate the intend rows to return.
It is specifically confusing to sea the description: between 20060430 and 20060501
and the actual command: BETWEEN '20060428' AND '20060504'

It seems pretty clear that this particular query should return all sample row shown no?

Like Indira I suspect you intend it to return just that single 4/30 row.
For that: BETWEEN '20060430' AND '20060504'

Those 'short' character values will effectively be expanded with all low values, and will thus match any time in the day.

If you put in BETWEEN '20060430' AND '20060501'
THen you get just the 4/30 records.

Now if you also wanted the 5/1 record you can ask for:
BETWEEN '20060430' AND '20060502'
or...
BETWEEN '20060430' AND '20060501Z'

That Z is higher then any first char for a Month and thus hight than any time to tday for any date.

Ditto on the other end.
If you specify: BETWEEN '20060430??????16' AND '20060504'

Then you are really effectively saying '20060430 16'.. which is lower than eny time because the first (most significant) Month char is lower then any real month.

If you really wanted the records for 4/28 after 16:00, then you pretty much must hard-code the redundant MonDay:
BETWEEN '20060428AprFri16' AND '20060501'

ps... my condolencences on the miserable data design you have to work with.
What a hoorible field, full of redundancies this is. What a lousy seperation of form and function (none). What terrible multi-lingual attributes. What a horrible missed opportunity in sorting order:
YYYYMMDDHHMMSI24MonDayHHMMSIAM would have made your query question trivial!

The Month and Day are entirely redundant with the date itself of coruse and can readily be calculated on the fly instead of being stored. Ditto for the 24H vs 12H time. Yikes.

As they say in France (and Canada I suppose :-) "Bon courage"... you'll need it with data like that.

Cheers,
Hein.

[Ratzie]

Yes, vendor apps can be a nightmare.
I knew I would have difficulty parsing down to the time field.

Appreciate all your help!