Oracle read DateTime problem - cannot read hour part of data
lazarus / 16-Databases

----------------------------------------------------------------------
mch_01 · 2012-10-10 10:20

I have tried to read data from oracle database using SQLQuery.
When I try to read data, I only recive the day part of datetime. Hour
and minutes are missing.

Example:

procedure TForm1.Button2Click(Sender: TObject);
var a:TDateTime;
begin
  OracleConnection1.Connected:=true;
  SQLQuery1.Close;
  SQLQuery1.SQL.Clear;
  SQLQuery1.SQL.Text:='SELECT SYSDATE DT FROM DUAL';
  SQLQuery1.Open;
  a:=SQLQuery1.FieldByName('DT').AsdateTime;
  Edit1.Text:=DateTimeToStr(a);
end; 

Do you have the same problem o know the solution?

regards,
MCh

----------------------------------------------------------------------
BigChimp · 2012-10-10 11:37

Same problem, Lazarus 1.0, FPC 2.6.0, Oracle 10

Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
var a:TDateTime;
begin
  OracleConnection1.Connected:=true;
  SQLQuery1.Close;
  SQLQuery1.SQL.Clear;
  SQLQuery1.SQL.Text:='SELECT SYSDATE DT FROM DUAL';
  SQLQuery1.Open;
  a:=SQLQuery1.FieldByName('DT').AsdateTime;
  showmessage('SYSDATE is ' +DateTimeToStr(a) + ' while fpc date is
'+DateTimeToStr(now()));
end;

The equivalent Firebird code does work correctly and returns
date+time:
Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
var a:TDateTime;
begin
  IBConnection1.Connected:=true;
  SQLQuery1.Close;
  SQLQuery1.SQL.Clear;
  SQLQuery1.SQL.Text:='SELECT CURRENT_TIMESTAMP DT FROM RDB$DATABASE';
  SQLQuery1.Open;
  a:=SQLQuery1.FieldByName('DT').AsdateTime;
  showmessage('SYSDATE is ' +DateTimeToStr(a) + ' while fpc date is
'+DateTimeToStr(now()));
end;

If a bug hasn't been filed yet and it hasn't been fixed in FPC trunk,
I suggest you file one.

----------------------------------------------------------------------
mch_01 · 2012-10-10 12:02

Probably this is a bug. i will file the case as the bug.

regards,
MCh

Problem explained:

From sqlplus:
SQL> SELECT SYSDATE DT FROM DUAL;

DT
--------
11/10/12

From the Oracle manual http://docs.oracle.com/cd/B19306_01/server.102/
b14200/functions172.htm: [^]
SYSDATE returns the current date and time set for the operating system
on which the database resides. The datatype of the returned value is
DATE.

Use the following code to get a date time value:
  SQLQuery1.SQL.Text:='SELECT TO_CHAR (SYSDATE, ''DD/MM/YYYY
HH24:MI:SS'') DT FROM DUAL';
  SQLQuery1.Open;
  a:=SQLQuery1.FieldByName('DT').AsdateTime;

Adapt to your date time locale.

Please use the forum or mailing list for these questions.

----------------------------------------------------------------------
ludob · 2012-10-11 11:53

In addition to the workaround in the bug report you can also use
Code: [Select]  SQLQuery1.SQL.Text:='SELECT CURRENT_TIMESTAMP DT FROM
DUAL';
  SQLQuery1.Open;
  a:=SQLQuery1.FieldByName('DT').AsdateTime;
That returns an Oracle timestamp which contains also the time
information.

----------------------------------------------------------------------
mch_01 · 2012-10-14 07:57

I think, the problem is much more complicated in Oracle.
There are two parameters in Oracle database. NLS_DATE_FORMAT and
NLS_TIMESTAMP_FORMAT. There is no DateTime type used in Oracle. Oracle
has internat type DATE. First format is used when you are trying to
get date from DATE column (for example select sysdate from dual).
Second, when you are trying to get TIMESTAMP column date.
Generally for database, first paratemetr is set without hours, second
with hours and seconds.
So, when you are calling DATE you will recive the information
TO_CHAR(SYSDATE,NLS_DATE_FORMAT). Whern you call TOMESTAMP, you will
recive the information
TO_CHAR(CURRENT_TIMESTAMP,NLS_TIMESTAMP_FORMAT).

This is the main cause while you sometimes hours is cutted, sometimes
no cutted.
To change this behavior we can try to ALTER SESSION in database, or
use direct call TO_CHAR(STSDATE,'YYYY-MM-DD H24:MI:SS) in queries (but
be carefull about date format - it shoul be the same in Oracle
database and local machine to conversion from Oracle to DateTme works
correctly).