Extracting dates with OCI

[Tve]

Hi,

I need to extract dates from an Oracle table: I am using the OCI functions.

When I use the "ociresult" for a date field, I get someting similar to (see last two colums):

[tt]
1088170292,1088170292,25-JUN-04,25-JUN-04
1088170292,1088170293,25-JUN-04,25-JUN-04
LS1,1088170297,1088170298,25-JUN-04,25-JUN-04
LS1,1088170299,1088170309,25-JUN-04,25-JUN-04
1088170297,1088170314,25-JUN-04,25-JUN-04
1088170316,1088170317,25-JUN-04,25-JUN-04
SAKOV,1088170311,1088170325,25-JUN-04,25-JUN-04
SST2,1088170323,1088170334,25-JUN-04,25-JUN-04
TAHAF,1088170300,1088170341,25-JUN-04,25-JUN-04
,1088170343,1088170343,25-JUN-04,25-JUN-04
A,1088170343,1088170344,25-JUN-04,25-JUN-04
SJAPED,1088170334,1088170345,25-JUN-04,25-JUN-04
[/tt]

What do I have to do to get the hours, minutes and seconds?

AD AUGUSTA PER ANGUSTA

Thierry

 

[Olavxxx]

I havent used Oracle DB, but doesent it use timestamp?
Please read up on this page:

http://www.databasejournal.com/features/oracle/article.php/2234501

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[Tve]

I had a quick read through the article, but basically, my problem is that I have a table with dates and I need to extract these values.

I could of course create a view on top of every table to format the date data with the TO_CHAR function, but this would mean maintenance. Furthermore, there must be a way to extract complete data from a date field instead of getting it "truncated".


AD AUGUSTA PER ANGUSTA

Thierry

 

[Tve]

I kept on looking and this seems to be the solution: redefine the default format for the connection

OCIExecute(OCIParse($oci_connect,"ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'"),OCI_DEFAULT);

Sometimes, it's just a matter of getting the right keywords in Google.


AD AUGUSTA PER ANGUSTA

Thierry

 

[Olavxxx]

uhm, on the page I gave you, there where several examples, amongst others:

TO_CHAR(date2,'MMDDYYYY:HH24:MI:SS') date2

I would say this is "the same"?

In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around the date...

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[Tve]

Well, it's not really the same.

In the TO_CHAR situation, you are defining columns. Here is a small example. I have a table defined as follows:

[tt]
CREATE TABLE ENTRIES
(
ID NUMBER NOT NULL,
FEATURE VARCHAR2(50),
HOST VARCHAR2(50),
USERNAME VARCHAR2(50),
INTTIMEIN NUMBER,
INTTIMEOUT NUMBER,
DTMTIMEIN DATE,
DTMTIMEOUT DATE
)
[/tt]

If I wanted to view this with the TO_CHAR solution, I would have to create a sql or view as follows:

[tt]
CREATE OR REPLACE FORCE VIEW FLEXLM.ENTRIES_CHAR
(ID, FEATURE, HOST, USERNAME, INTTIMEIN,
INTTIMEOUT, DTMTIMEIN, DTMTIMEOUT)
AS
SELECT "ID","FEATURE","HOST","USERNAME","INTTIMEIN","INTTIMEOUT",TO_CHAR("DTMTIMEIN",'YYYY-MM-DD:HH24:MI:SS'), TO_CHAR("DTMTIMEOUT",'YYYY-MM-DD:HH24:MI:SS') FROM ENTRIES
[/tt]

The main impacts of this solution are:
[ul][li]You have to maintain the views/sql statements to keep them in line with the tables.[/li][li]The function "OCIColumnType" will return a type VARCHAR for the column.[/li][/ul]

The "ALTER SESSION SET NLS_DATE_FORMAT" modifies the output for dates, but not the underlying data: "OCIColumnType" will still return "DATE".

HTH




AD AUGUSTA PER ANGUSTA

Thierry

 

[Olavxxx]

Cant you do a:
TO_CHAR("DTMTIMEOUT",'YYYY-MM-DD:HH24:MI:SS') AS newdate

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[Tve]

Yes, but you're still creating extra views and date data is string format

AD AUGUSTA PER ANGUSTA

Thierry

 

[Olavxxx]

Give yourself a star then?

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster