Seal Report Forum
»
Report Edition
»
Reports
»
Oracle Date Format Inconsistencies in Restrictions
Rank: Member
Groups: Registered
Joined: 6/9/2017(UTC) Posts: 29  Thanks: 1 times
|
Hi,
I'm running into inconsistent behaviors when using dates in restrictions in the Web Report Designer.
Seal is running on a web server where Windows is configured with the 'English (United States)' default settings, including dates in the format 'M/d/yyyy'.
The Seal Server Manager is configured with the date format 'dd-MMM-yyyy' to match the default Oracle date format.
The date fields are all defined to use the server's default date format using the 'd' format.
When creating restrictions in the Web Report Designer, the dates are displayed as expected (MM/dd/yyyy), but when executing the report, the generated Select statement contains dates in the display format (MM/dd/yyyy), which generates the error: ORA-01843: not a valid month.
The only way I can get valid Select statements is to set the date format to 'dd-MMM-yyyy' on the database connection, but then all dates in the reports are displayed in the 'dd-MMM-yyyy' format. Moreover, the date restrictions don't return the expected records.
On a different server, configured with the Canadian defaults, using the default date format 'd' everywhere in Seal performs as expected. Although, looking at the generated Select in Oracle, the date format is 'yyyy-MM-dd'. This format should generate the same error listed above (ORA-01843).
It looks like Seal performs some internal manipulations on the dates before the report runs, yielding inconsistent results. Either that or I must be missing something fundamental along the way. I'm open to setup a Webex session to demonstrate the issue to quickly resolve this situation.
Yves
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209  Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
|
Hi Yves, thank you for the feedback.
According to the code, if the database is Oracle, the following command is sent when opening the connection:
"alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'";
this is weird and I wonder how it impacts the behavior, perhaps consider to switch back to database type = Standard to check how it goes.
otherwise the date format specified in the connection is only used to generate the SQL for a date restriction.
so if you set the date format to 'dd-MMM-yyyy' on the database connection, the SQL generated for a date restriction will be = '25-JAN-2021' for example, so I would recommend to let the default Ansi format "yyyy-MM-dd HH:mm:ss", check the SQL generated and your Oracle settings.
it has normally no impact on the format used to display a date (either in a report element or a restriction).
the date format 'd' is of course dependent of the locale of the server (Canadian or US).
|
|
|
|
|
|
Rank: Member
Groups: Registered
Joined: 6/9/2017(UTC) Posts: 29 Thanks: 1 times
|
Eric,
I experimented with the suggestions you made, here is the combination of settings that generated the expected results:
Server Configuration:
Culture: English (United States)
Date Time Format: d
Connection:
Database type: Oracle
Date Type format: yyyy-MM-dd HH:mm:ss
From these settings, I gather that the date format used to display dates to the users is obtained from the Server Configuration settings. The date format used in Select statements is obtained from the Connection settings, but the 'alter session' statement doesn't use this setting and alway uses the default Ansi format. The 'alter session' statement should use the Connection setting.
Things seem to be working fine for now, but if I discover other issues, is there a way to do an 'alter session' statement of my own inside the Report Execution Init Script?
Yves
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209 Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
|
After checking the code source,
I would suggest to use the 'Pre SQL Statement' property of your Data Source.
It will be executed just after opening the connection when querying the database.
|
|
|
|
|
|
Rank: Member
Groups: Registered
Joined: 6/9/2017(UTC) Posts: 29 Thanks: 1 times
|
Merci Eric. Putting the 'alter session' statement in the 'Pre SQL Statement' generated the expected results.
Yves
|
|
|
|
|
|
Seal Report Forum
»
Report Edition
»
Reports
»
Oracle Date Format Inconsistencies in Restrictions
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.
