Oracle 9 ODBC and timestamp datatypes

[lyanch]

I am having difficulty using Oracles ODBC connection (and the native connection and Crystals for that matter) and the timestamp datatype. I don't need the whole timestamp info, just the datetime portion. Either way the ODBC connection gives the following error when including any timestamp columns:

SQLDescribeCol Failed

Any suggestions?

Thanks in advance

Lisa

 

[synapsevampire]

I'll be converting the timestamp in the view or SP for my current project (or you could do so using the SQL Expression).

I don't have 9i here to test today, but this shows that the timestamp can be cast.

http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/functions15.htm

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lyanch]

Thanks... this was exactly the direction i was looking for. Crystal was a bit odd about the cast call however. IT still insisted that the "date" was a string. Then all of a sudden it started giving SQL errors???? I couldn't get it to start working again so I used a to_char and a to_date:

to_date(TO_CHAR(TIME,'DD-MON-YY HH.MI.SS PM'),'DD-MON-YY HH.MI.SS PM')

Which works fine, even if I don't like having to do the double cast. Just using TO_DATE on the timestamp caused a SQL error as well (everywhere not just in Crystal).

Lisa

This came through fine.

 

[synapsevampire]

That is odd...if you run a CAST from SQL Plus it gives you an error?

Have you worked with timezones yet?

If so, I'd be very interested to hear how you're using them.

One thought I had was to convert the timezone portion in my View or SP as a separate field when I need to use it.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

OK, I saw strange behavior trying to use a SQL Expression in CR to cast just a date field, though it worked in SQL Plus.

I guess Crystal doesn't like Cast.

<unsurprised look>

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lyanch]

I haven't gotten to the timezone stuff yet, but I am pretty sure its coming. For this report all the timestamps are in the same zone, but the next report (they are still working on definitions) I know deals with worldwide time zones... so.. will let you know when I get there.

Lisa

 

[synapsevampire]

Thanks, I'd be interested to read what you are doing, and why.

Our intent is to provide reporting for either UNC or Local time, hence the timezones.

Since we have a data warehouse of sorts, we currently have 2 fields in there, UNCtime and Localtime. I'm wondering about performance of the SQL when I use a single field with timezone and convert when required, vs. 2 fields

Performance more important to me than the maintenance of 2 fields.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com