Modifying a query to work without requiring NLS_DATE_FORMAT to be set

Question

I the following query:

SELECT
    trunc(estimatedenddate,'hh') AS reg_date,
    COUNT(*)
FROM
    (
        SELECT
            attr_value,
            TO_DATE( (DATE '1970-01-01' + (1 / 24 / 60 / 60 / 1000) * attr_value),'yyyy-mm-dd HH24:mi:ss') AS estimatedenddate
        FROM
            attr
        WHERE
            attr_name = 'createTimestamp'
    )
WHERE
    estimatedenddate > TO_DATE('01/JUN/2018','dd/mon/yyyy')
GROUP BY
    trunc(estimatedenddate,'hh')
ORDER BY
    reg_date DESC;

It works when I set the NLS_DATE_FORMAT. See dbfiddle.

However if I do not set the NLS_DATE_FORMAT, the query doesn't produce any results. See dbfiddle

How can I modify this query such that it works without setting the NLS_DATE_FORMAT?

The mistake in your code is that you have something that is already a date, namely `DATE '1970-01-01' + (1/24/60/60/1000) * attr_value`, and you wrap it within `TO_DATE`. **REMOVE** that call to `TO_DATE` (and the format model that goes with it), the rest should work just fine. — , Jan 14 '19 at 23:15
... [as you've been told before](https://stackoverflow.com/q/54155071/266304) *8-) — Alex Poole, Jan 14 '19 at 23:22
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=3222bcd64cd7816b218e0ba00cb5dedb — Alex Poole, Jan 14 '19 at 23:32

score 4 · Accepted Answer · answered Jan 15 '19 at 00:41

TO_DATE( date_string, format_model ) takes a string for the first argument but you are passing a DATE data type which Oracle will try to be helpful and implicitly convert to the expected string data type using its default format model; so your inner query is effectively:

SELECT attr_value,
       TO_DATE(
         TO_CHAR(
           DATE '1970-01-01' + (1 / 24 / 60 / 60 / 1000) * attr_value,
           (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
         ),
         'yyyy-mm-dd HH24:mi:ss'
       ) AS estimatedenddate
FROM   attr
WHERE  attr_name = 'createTimestamp'

Instead you can just remove the TO_DATE() function:

SELECT trunc(estimatedenddate,'hh') AS reg_date,
       COUNT(*)
FROM   (
  SELECT DATE '1970-01-01' + (1 / 24 / 60 / 60 / 1000) * attr_value AS estimatedenddate
  FROM   attr
  WHERE  attr_name = 'createTimestamp'
)
WHERE    estimatedenddate > DATE '2018-06-01'
GROUP BY trunc(estimatedenddate,'hh')
ORDER BY reg_date DESC;

Modifying a query to work without requiring NLS_DATE_FORMAT to be set

1 Answers1