CODEWITHSUNDEEP
sqloracle-databasedatetimestamp
Arockia Raj
Arockia Raj

Reputation: 51

Oracle Timestamp fails at condition

I have table systemproperties, which stores propertyname (varchar) and propertyvalue (clob). Under that one of the properties are,

propertyname - RowDate
propertyvalue - 12-MAY-17 04.00.06.883000 PM

And another table activity, I am storing the log data with timestamp(6).

Query to fetch log data from activity table,

select type,startdate from activity where startdate > ?

Here startdate is timestamp(6) type. I am using the actual value and the results are nothing. But, if I use as below:

select type,startdate from activity 
where startdate > (select to_char(propertyvalue) 
                   from systemproperties where propertyname='RowDate')

I am getting the rows greater than the time. I am unable to understand the difference here. Could any one guide me here. What can I use for the solution?

Here are my NLS Settings:

NLS_LANGUAGE    AMERICAN
NLS_TERRITORY   AMERICA
NLS_CURRENCY    $
NLS_ISO_CURRENCY    AMERICA
NLS_NUMERIC_CHARACTERS  .,
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE   AMERICAN
NLS_SORT            BINARY
NLS_TIME_FORMAT     HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY   $
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP FALSE

Thanks in advance.

Upvotes: 0

Views: 193

Answers (2)

MT0
MT0

Reputation: 167877

When you compare startdate > your_property_string, Oracle will implicitly try to convert the property string to a matching data type (i.e. TIMESTAMP) using the appropriate NLS_SESSION parameter as the format mask. So the WHERE clause will be implicitly converted to the equivalent of:

WHERE startdate > TO_TIMESTAMP(
                    your_property_string,
                    ( SELECT value
                      FROM   SYS.NLS_SESSION_PARAMETERS
                      WHERE  parameter = 'NLS_TIMESTAMP_FORMAT' )
                  )

So, Oracle can give different values for your property string depending on what your NLS_TIMESTAMP_FORMAT is set to. I.e. if it is YYYY-MON-DD HH12:MI:SS.FF6 AM then your date would be parsed as 0015-05-17T16:00:06.883000 and if it was DD-MON-YY HH12:MI:SS.FF6 AM then it would be 2017-05-15T16:00:06.883000.

If you want to be consistent when converting the string then you will have to explicitly specify the format mask in the query (and the NLS_DATE_LANGUAGE):

SELECT type,
       startdate
FROM   activity 
WHERE  startdate > TO_TIMESTAMP(
                     ?,
                     'DD-MON-YY HH12.MI.SS.FF6 AM',
                     'NLS_DATE_LANGUAGE=ENGLISH'
                   )

Upvotes: 1

APC
APC

Reputation: 146209

Your property is defined like this:

propertyvalue - 12-MAY-17 04.00.06.883000 PM

Now you think that means 12th May 2017 but that's not what you've said. So when casting the string 12-MAY-17 04.00.06.883000 PM to a date Oracle does what it usually does, and defaults the century to 00.

Demo:

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-05-14 23:50:09

SQL> select to_date('17-05-14 00:00:00') my_date from dual;

MY_DATE
-------------------
0017-05-14 00:00:00

SQL>

That's why you get the results you do. Obviously you have a lot of records where startdate > date '0017-05-12'

"What can I use for the solution?"

The best solution is to define the date properly. It is 2017 and that's way too late to still be coding the Y2K bug.

propertyvalue - 12-MAY-2017 04.00.06.883000 PM

Alternatively you can use an explicit date mask when you reference the property:

startdate > to_date(propertyvalue, 'DD-MON-YY HH.MI.SS.FF6 AM')

In this case Oracle will substitute the current century 20 for the missing element of the mask. It's still a bug waiting to happen but 20 is more likely to be the right answer more often than 00.

"startdate is a timestamp(6)"

Doesn't matter, Oracle treats them both the same in this respect:

SQL> alter table t23 add ts timestamp;

Table altered.

SQL> update t23 set ts = systimestamp;

4 rows updated.

SQL> select count(*) from t23 where ts > to_date('17-05-16 00:00:00');

  COUNT(*)
----------
         4

SQL> select count(*) from t23 where ts > to_date('17-05-16 00:00:00', 'yy-mm-dd hh24:mi:ss');

  COUNT(*)
----------
         0

SQL>

Upvotes: 1

Related Questions