BUG: Oracle date format ignored

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
albourgz
Posts: 160
Joined: Wed 06 May 2009 12:17
Location: belgium

BUG: Oracle date format ignored

Post by albourgz » Fri 03 Nov 2017 14:50

Using c++ builder tokyo 10.2.1, unidac 7.1.4.
new vcl project
->TOracleUniProvider
->TUniConnection, oracle connection in direct mode, settings: UseUnicode true, UnicodeEnvironment true, dateFormat YYYY-MM-DD, dateLanguage french, schema cec. Connect.
->TUniQuery->Select * from table
->TUniDataSource linked to TUniQuery
->TCRDBGrid linked to TUniDataSource
=> Date fields appear as DD-MM-YY.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: BUG: Oracle date format ignored

Post by MaximG » Mon 19 Feb 2018 10:18

To display the date in the format you need using any DB Aware components (DBGrid, CRDBGrid, etc.),
use the DisplayFormats property of the corresponding TDateTimeField: docwiki.embarcadero.com/Libraries/Tokyo ... playFormat

albourgz
Posts: 160
Joined: Wed 06 May 2009 12:17
Location: belgium

Re: BUG: Oracle date format ignored

Post by albourgz » Mon 19 Feb 2018 12:06

I don't have any TDateTimeField.
I just have a TUniQuery "select * from table" displaying content in TCRDBGrid.
The date-time columns should respect format given in TUniSession.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: BUG: Oracle date format ignored

Post by MaximG » Tue 20 Feb 2018 13:15

According to our documentation (devart.com/unidac/docs/?oraprov_article.htm), the DateFormat option specifies the date format for conversion to a string on the server side. When specifying this property (for example, 'YYYY-MM-DD'), the following query is executed: ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'. The result of its work is demonstrated by the following query: SELECT SYSDATE AS DATETIMEVALUE, TO_CHAR(SYSDATE) AS STRINGVALUE FROM DUAL

albourgz
Posts: 160
Joined: Wed 06 May 2009 12:17
Location: belgium

Re: BUG: Oracle date format ignored

Post by albourgz » Tue 20 Feb 2018 13:31

The problem is in TCRDBGrid.

Code: Select all

SQL> desc cec.joblist;
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(9)
 DTIN                                      NOT NULL DATE
...
SQL> select dtin from cec.joblist where rownum<2;

DTIN
--------
30/12/99

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

Session modifiÚe.

SQL> select dtin from cec.joblist where rownum<2;

DTIN
----------
1899-12-30
If I create a vcl app with tuniconnection, direct mode to oracle, date format='YYYY-MM-DD', tuniquery with select dtin from cec.joblist where rownum<2, tunidatasource and tcrdbgrid, the value displayed is 20-04-17 14:20:23. So TCRDBGrid totally ignores the date format of TUniConnection.
Even executing alter session in a tuniquery DOESN'T change the way date/times are displayed in tcrdbgrid.
This was working fine with ODAC!
So question is : how do I ask TCRDBGrid to display date/times the way chosen in TUniSession?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: BUG: Oracle date format ignored

Post by MaximG » Wed 21 Feb 2018 08:16

To investigate the issue, please compose and send us a small sample, in which the issue occurs. This is convenient to do this via the e-support form ( devart.com the "Support"\"Request Support" menu)

Post Reply