How to control date format generated for Oracle query

building semantic layer
1

This is what is generated.
’01-10-2014 00:00:00’

I need this.
TO_DATE(’01-10-2014 00:00:00’,’DD-MM-YYYY HH24:MI:SS’)

How do I change the parameter in the file to accomplish.

From the Oracle.prm file.
‘dd-MM-yyyy HH:mm:ss’
.
DD-MM-YYYY HH24:MI:SS

cmmrfrds (BOB member since 2007-12-11)

2

Are you sure you have time stamp values in the DB field concerned, when you query the DB directly?

Mak 1 :uk: (BOB member since 2005-01-06)

3

An update. We tested and have potential solution.

Here is some back ground.
BOE oracle.prm file.
Default format:
DD-MM-YYYY HH24:MI:SS
Changed to:
DD-MMM-YYYY

Oracle NLS_DATE_FORMAT setting in Oracle.
‘DD-MON-RR’

We use this literal format in our SQL commonly to achieve static pruning.
‘DD-MON-YYYY’

Example using BOE Oracle default.

–alter session set NLS_DATE_FORMAT=‘DD-MM-YYYY HH24:MI:SS’;
select
*
from clarity.clarity_tdl_tran tran
where tran.post_date = ‘16-12-2014 00:00:00’

Example using new format in BOE to match the Oracle NLS_DATE_FORMAT.

select
*
from clarity.clarity_tdl_tran tran
where
tran.post_date = ‘16-DEC-2014’

The Explain Plan cost was 13757 in the 1st case and 19 in the second which was a dramatic improvement. Basically, we needed to drop the time in the parameter to match the Oracle default NLS_DATE_FORMAT. The downside is that we cannot put time in the Where Clause WEBI filter, but I think we can live with that to gain the benefit of static pruning in Oracle.

cmmrfrds (BOB member since 2007-12-11)

4

From your original post, I didn’t think it would be related to NLS settings on the Oracle side.
Very few people want prompts with time stamps in any case, as they are too granular.

Mak 1 :uk: (BOB member since 2005-01-06)

5

Here is some further explanation of why we want a different Oracle date format.

When we partition a table by date in Oracle we use this mask ‘syyyy-mm-dd hh24:mi:ss’.
When we use the NLS_DATE_FORMAT or include a TO_DATE with a mask.
Either tran.post_date = ‘16-DEC-2014’ or tran.post_date = to_date( ‘16-12-2014 00:00:00’,‘DD-MM-YYYY HH24:MI:SS’) then the Oracle optimizer does access(“TRAN”.“POST_DATE”=TO_DATE(’ 2014-12-16 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’)) which does static pruning while with the BOE Oracle default it does not.
select – BOE Default
*
from clarity.clarity_tdl_tran tran
where
tran.post_date = ‘16-12-2014 00:00:00’
Explain Plan result.
4 - access(“TRAN”.“POST_DATE”=‘16-12-2014 00:00:00’)
select – NLS_DATE_FORMAT
*
from clarity.clarity_tdl_tran tran
where
tran.post_date = ‘16-DEC-2014’
Explain Plan result.
4 - access(“TRAN”.“POST_DATE”=TO_DATE(’ 2014-12-16 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’))
select – Add a TO_DATE with mask
*
from clarity.clarity_tdl_tran tran
where
tran.post_date = to_date( ‘16-12-2014 00:00:00’,‘DD-MM-YYYY HH24:MI:SS’)
Explain Plan result.
4 - access(“TRAN”.“POST_DATE”=TO_DATE(’ 2014-12-16 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’))

cmmrfrds (BOB member since 2007-12-11)