Using LIKE for Date Ranges??? 2

[thumper808]

Hi Guys, yet again, I am requesting knowledge from the all-powerful Tek-Tips team. First things first...
1. I have an Oracle database that has a column for a datetime stamp. This datetime stamp (ie: 28 Apr 2005 08:44) is not stored as a datetime datatype, it's stored as a varchar string datatype.
____________________
2. I have a Crystal Reports 10 report that has a FromDate and a ToDate parameter which I am calling in the Select Expert in the following manner... {UD_CALL.DateColumn} in {?FromDate} to {?ToDate}
_____________________
3. The problem that I've encountered is if I select FromDate as 01 Apr 2005 00:01 and ToDate as 30 Apr 2005 23:59 I get the following: (just a sample)
07 Apr 2005 10:11
07 Apr 2005 10:42
07 Apr 2005 15:44
23 Mar 2005 09:35
24 Mar 2005 09:38
02 May 2005 08:34
26 Apr 2005 08:42
_____________________
4. Now, I don't care what order the data is in for the BUT I do care that I didn't even ask for March or May! I am not saying I'm an expert at this but my suspicion is that my problem lies in the datatype area.

If I go into the Oracle's SQL editor, I run the following statement as an experiment:
select DateColumn from ud_call where "UD_CALL"."DateColumn" like '04 Apr 2005%'
- What I get is exactly what I want...all entries that start with 04 Apr 2005 ---Which is great BUT [highlight]how can I tell Crystal Reports to search on 2 dates using the LIKE syntax so I can get the range I so desperately want accurately?[/highlight]

I have tried every imaginable solution. Please, any help would be useful. Thank you and have a great day.

Thumper

 

[synapsevampire]

First, drag your dba through a trough of shattered Oracle training CD's prior to launching she/he out the thickest safety free glass window in the building.

Create a SQL Expression to cast the varchar date to a real date type, then you can reference that date instead of the varchar.

Are you certain that the example date doesn't have seconds in it?

If you need help with this, post, but please post the exact format of the data in the varchar field when doing so.

-k

 

[thumper808]

Ah synapsevampire...too funny...yeah, trust me, I DO want to drag the DBA through razor blades and soak him in iodine! As for your question, thoses dates I gave are, in fact, the format I gave. The actual data type is VARCHAR2(2000)...according to my sql editor. There are no seconds in the data.
As for the the SQL Expression...I'm going to have to read up on that...I've not done one yet in my course of work...Thank you for the prompt reply synapsevampire...

 

[synapsevampire]

SQL Expressions are fairly simple, they're just SQL.

Ask the offending dba to give you the Oracle syntax to convert the pseudo date into a real date, and then just paste it into a SQL Expression.

Note that you won't use the SELECT part of it, just the CAST or CONVERT or TO_DATE function they give you.

What occurs is that it gets appended to the SELECT portion of the query generated by Crystal, as in:

select CAST(table.field as date) as MyDate, field1, field2,... from table

-k

 

[kskid]

The SQL expression would be something like

//%xxxDate
(
to_date({table.datefield},'DD Mon YYYY HH24:MI')
)


and in your record selection


{%xxxDate} in YearToDate

-LW

 

[davidwendelken]

How certain are you that only valid dates will be put into this VARCHAR2(2000) field?

You may get some runtime errors using the to_date function if they put in things like '23 Mayy 2005 14:75'.

Best fix would be to alter the table definition.
Of course, that would require someone to admit to a mistake, so that may not be a viable option.

Next best would be to put an insert/update trigger on the table to verify the date is valid.

Failing that, create a database function that accepts a string and returns a date, but doesn't treat all non-date input values as an error. Have it return a "really early" or "really late" date if it's in error so the data sorts to the front or back of the list. Pick a date that wouldn't correctly occur in your data. A similar function for returning a value to be printed could return "invalid date" for the bad ones.

If the above can't be done, use the sql expressions as noted above. And let the DBA know that management will learn that the project cost and time overruns will be due to the DBAs mistakes and unwillingness to correct them.



David Wendelken

http://www.casetech.net

 

[synapsevampire]

Good points, David, although changing the date to an early or late date should be a last resort, I would raise an error and make the user redo the data.

Why people are employed that don't know how to create an edit mask and perform validation always astonishes me, almost every language has had them for 20 years...

-k

 

[thumper808]

Thank you all for the responses...Oracle is giving me the grief now...not your problem...I did all the solutions and they seemed to work. Believe it or not, the DBA who created the database is here with me now and he still refused to change the datatype. Ah what fun. Thank you so much for the help!

 

[synapsevampire]

It may be that you're using the wrong connectivity.

Use either the Crystal supplied Oracle ODBC driver, or the Crystal native connectivity for Oracle, NOT the Oracle supplied ODBC driver.

-k