How do I compare an oracle timestamp with a given text?

I am using c# with the crystal reports that comes with visual studio and an oracle database. I have two datetimepickers and need to query the Birthdays table which has a TIMESTAMP column. When I pull out the Timestamp as a test I get:

17-OCT-05 10.00.00.000 AM +00:00

I need to do the comparison in the SelectionFormula. Let me know if I am not clear enough.
LVL 3
zstafaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SpykairCommented:
IF you only want the date and not the time, then right click on the column -> format -> Date and Time tab -> select the format you desire or customise the format.

Hth,
Spykair
0
zstafaAuthor Commented:
I need date and time, and there is no column for timestamp. Thanks.
0
wykabryanCommented:
Provided the datatype in Oracle is datetime than a simple:

Timestamp in {datetimepicker1} to {datetimepicker2}

or
datetime(Timestamp) in {datetimepicker1} to {datetimepicker2}
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

zstafaAuthor Commented:
the datatype in Oracle is Timestamp. Thanks
0
wykabryanCommented:
Do you receive an error when you try the above solution?  Reading CR help file, it states" When logging on to Oracle using crdb_oracle.dll, the date format is changed to match the default date/time formate of Crystal Reports."

To me this would mean that even though it displays that way, the way CR interpets the timestamp it will convert it to the default datetime (MM/DD/YYYY HH:MM:SS)..

0
zstafaAuthor Commented:
The error I get when I use the above is

"the remaining text does not appear to be part of the formula"
0
zstafaAuthor Commented:
Sorry now I get

"A number, currency amount, boolean, date, time, date-time, or string is expected here"

 
0
wykabryanCommented:
I think what you are going to be reduced to doing is converting the timestamp to "to_date" within Oracle's database before you pull it into the report.

Here are some articles on that process.

http://blogs.ittoolbox.com/database/solutions/archives/006681.asp?rss=1
http://www.databasejournal.com/features/oracle/article.php/2234501
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10749/ch4datetime.htm

I do not think there is a way.. maybe someone else has an idea.  But I would think this is probably going to be the least amount of work.  
0
zstafaAuthor Commented:
Is there a way of taking care of the conversion in the sql statement? I don't need to pull the timestamp into the report, just use it to compare.

Thanks
0
wykabryanCommented:
sql statement, I would assume the same as a datebase view -> Command Object.  It has been 3 years since I have touch Oracle.  So I am not sure I could write the right statement for you.  But I do believe those hyperlinks could provide the information for you.
0
zstafaAuthor Commented:
This statement works fine in the sqlplus console but does not work in the crystal reports viewer selectionformula

'Select * from Events WHERE Events.Timestamp >= to_timestamp('4/20/2006 1:52:49 PM','MM/DD/YYYY HH:MI:SS AM')
AND Events.Timestamp <= to_timestamp('4/21/2006 1:52:49 PM','MM/DD/YYYY HH:MI:SS AM')'

I get "A number, currency amount, boolean, date, time, date-time, or string is expected here" error. Any Ideas?

0
wykabryanCommented:
Here is what I would suggest, create a database view something like this,

create or replace view vrpt_name (man, cant even remember the syntax for a view creation...its been a while)
select *, to_date(timestamp)  <- Not quiet sure on the to_date conversion
from
events


then pull this view into the report.  Now that you have it in date format you can do your compare.


0
zstafaAuthor Commented:
is it possible to do that in a selectionformula string?
0
wykabryanCommented:
No, because of the way CR uses the date features is very different than Oracle, as opposed to SQL Server.  It appears that Oracle has 2 date time datetypes; timestamp and date.  Timestamp is more of a text field, CR is having issues understanding how to proceed with the conversion.  That is why it has to be done in a command object or in a database view.
0
zstafaAuthor Commented:
I have no problems doing it in a command object but how do I get the table back into the crystal reports viewer?

Thanks
0
wykabryanCommented:
ok.. I believe in oracle the datetype remains constant in lay out.  This formula should provide you the solution you are looking for.

Formula Name: ConvertedDate

Datetime(date(left({Events.Timestamp},9)),
              time(mid(replace({Events.Timestamp},'.',':'),11,8)+' '+
                      mid({Events.Timestamp},24,2)))


Then go to Report>Selection Formulas>Record

{ConvertedDate} in datetime('4/20/2006 1:52:49 PM') to datetime('4/21/2006 1:52:49 PM')
0
zstafaAuthor Commented:
when I check it in the formula editor, I get an error

"A blob field cannot be used in a formula"

It is pointing at the Events.Timestamp

Thanks
0
wykabryanCommented:
hmmm.. strange why a timestamp column is set up as a blob.  This is very similar to a memo field in Access.  Can you use a totext function to convert it over.  To test just write a simple formula
left(totext({Event.TimeStamp}),5) <--no relation to anything just a number for testing.  

In theory, this conversion should work.  If so, wrap Event.Timestamp with the totext function in the formula provide above.
0
zstafaAuthor Commented:
"A blob field cannot be used in a formula" on {Event.TimeStamp}
0
wykabryanCommented:
drats.. looks like if you want this to work you are going to have to create a database view or command object and then set the location of the report from the table to the view.  This will allow you to convert the blob over to a text field or convert it to a date field.  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zstafaAuthor Commented:
I have created a command object and am able to display the timestamp using reader.GetOracleTimestampTZ(1) and converting it to a string. Where do I "set the location of the report from the table to the view"?

Thanks
0
wykabryanCommented:
In the report go to Database> Set Datasource Location

There you will choose the table that you want to set in the top portion called Current Data Source.  Then in the below section, Replace with, you will choose the command object and then click update.  
0
zstafaAuthor Commented:
OK I am almost there. The dataset gets filled with the correct info but the crystalreportsviewer is always blank. What am I missing here?

CrystalReport crnew= new CrystalReport();
crystalReportViewer1.ReportSource = crnew;
crystalReportViewer1.ShowRefreshButton =false;
crystalReportViewer1.ShowCloseButton = false;
crystalReportViewer1.ShowGroupTreeButton = false;

string cmdQuery ="Select * From EVENT_TABLE WHERE EVENT_TABLE.TIMESTAMP BETWEEN TO_TIMESTAMP('"+dateTimePicker1.Value.ToUniversalTime()+"', 'MM/DD/YYYY HH:MI:SS PM') AND TO_TIMESTAMP('" + dateTimePicker2.Value.ToUniversalTime() +"', 'MM/DD/YYYY HH:MI:SS PM')";

string constr = "User Id=xxxx;Password=xxxx;Data Source=xxxx;";
string cmdstr = cmdQuery;
 
OracleDataAdapter adapter = new OracleDataAdapter(cmdstr, constr);
OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
 
// Create and fill the DataSet
DataSet dataset = new DataSet("Alarms");
adapter.Fill(dataset);
crnew.SetDataSource(dataset);      
MessageBox.Show(dataset.Tables[0].Rows.Count.ToString());
0
zstafaAuthor Commented:
OK I figured it out, was not filling the table. Thanks everyone for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.