[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-04-20
24
Medium Priority
?
865 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:zstafa
  • 13
  • 10
24 Comments
 
LVL 10

Expert Comment

by:Spykair
ID: 16499490
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
 
LVL 3

Author Comment

by:zstafa
ID: 16499679
I need date and time, and there is no column for timestamp. Thanks.
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 16500656
Provided the datatype in Oracle is datetime than a simple:

Timestamp in {datetimepicker1} to {datetimepicker2}

or
datetime(Timestamp) in {datetimepicker1} to {datetimepicker2}
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
LVL 3

Author Comment

by:zstafa
ID: 16501010
the datatype in Oracle is Timestamp. Thanks
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 16501102
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
 
LVL 3

Author Comment

by:zstafa
ID: 16501468
The error I get when I use the above is

"the remaining text does not appear to be part of the formula"
0
 
LVL 3

Author Comment

by:zstafa
ID: 16501556
Sorry now I get

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

 
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 16501615
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
 
LVL 3

Author Comment

by:zstafa
ID: 16501735
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
 
LVL 16

Expert Comment

by:wykabryan
ID: 16501782
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
 
LVL 3

Author Comment

by:zstafa
ID: 16509277
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
 
LVL 16

Expert Comment

by:wykabryan
ID: 16509377
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
 
LVL 3

Author Comment

by:zstafa
ID: 16524399
is it possible to do that in a selectionformula string?
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 16524431
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
 
LVL 3

Author Comment

by:zstafa
ID: 16524726
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
 
LVL 16

Expert Comment

by:wykabryan
ID: 16524941
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
 
LVL 3

Author Comment

by:zstafa
ID: 16525547
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
 
LVL 16

Expert Comment

by:wykabryan
ID: 16525594
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
 
LVL 3

Author Comment

by:zstafa
ID: 16525654
"A blob field cannot be used in a formula" on {Event.TimeStamp}
0
 
LVL 16

Accepted Solution

by:
wykabryan earned 1500 total points
ID: 16525751
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
 
LVL 3

Author Comment

by:zstafa
ID: 16526244
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
 
LVL 16

Expert Comment

by:wykabryan
ID: 16526276
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
 
LVL 3

Author Comment

by:zstafa
ID: 16543624
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
 
LVL 3

Author Comment

by:zstafa
ID: 16562579
OK I figured it out, was not filling the table. Thanks everyone for your help.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Screencast - Getting to Know the Pipeline
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month17 days, 16 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question