asked on

to_char not giving the right result

SELECT username,
timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = 'ABC'
AND to_char(timestamp,'DD-MON-YY') Between '01-APR-10' AND '13-APR-10'
AND Username = 'XYZ'
ORDER BY timestamp;

Kamal Agnihotri

ASKER

My query is not giving the expected result. From the query above, I am getting data range outside of what I specified.

Sean Stuber

that's because you're doing string comparisons instead of date comparisons

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nole172

If your timestamp contains a time component, try:

SELECT username,
timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = 'ABC'
AND trunc(timestamp) Between '01-APR-10' AND '13-APR-10'
AND Username = 'XYZ'
ORDER BY timestamp;

Sean Stuber

no

trunc(timestamp) Between '01-APR-10' AND '13-APR-10'

compares a date to a two strings

if you want to get all data for the 13th of april then adjust the date range

SELECT username,
timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = 'ABC'
AND timestamp Between to_date('01-APR-2010','dd-MON-yyyy') AND to_date('13-APR-2010','dd-MON-yyyy') + 1
AND Username = 'XYZ'
ORDER BY timestamp;

or more accurately

SELECT username,
timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = 'ABC'
AND timestamp >= to_date('01-APR-2010','dd-MON-yyyy') AND
timestamp < to_date('13-APR-2010','dd-MON-yyyy') + 1
AND Username = 'XYZ'
ORDER BY timestamp;

the reason for the difference is between is inclusive so 2010-04-14 00:00:00 would be included

Sean Stuber

the reason

trunc(timestamp) Between '01-APR-10' AND '13-APR-10'

doesn't work is because the different data types (dates and strings) forces implicit conversion
this is not only slower (not indexable by default) but also error prone since your implicit conversion could be in any format. even if you force the conversion to be in dd-mon-yy format
you're simply pushing the problem back to the original issue that strings of the format above don't sort like dates.

'11-NOV-2010' is between the two strings above

furthermore, the above strings are reinventing the y2k bug.
so better is to use dates to represent dates when possible and not strings because it avoids the y2k ambiguity, as well as addressing all of the other problems above

nole172

My mistake, sdstuber. Good catch.

Sean Stuber

glad I could help, both of you! :)

nole172

nole172@PROD> select * from test;

ONEFIELD DATEFIELD
-------------------- ---------------
Yup 14-APR-10 18:04
Yup 14-APR-10 18:04
Yup 14-APR-10 18:04
Yup 14-APR-10 18:04
Yup 14-APR-10 18:04
Yup 14-APR-10 18:04
Yup 14-APR-10 18:04
No 14-NOV-10 00:00

8 rows selected.

nole172@PROD> select * from test where trunc(datefield) between '14-APR10' and '15-APR-10';

ONEFIELD DATEFIELD
-------------------- ---------------
Yup 14-APR-10 18:04
Yup 14-APR-10 18:04
Yup 14-APR-10 18:04
Yup 14-APR-10 18:04
Yup 14-APR-10 18:04
Yup 14-APR-10 18:04
Yup 14-APR-10 18:04

7 rows selected.

SDSTUBER: Just to throw in one more comment.  I don't know what I am missing, but my statement seems to work in Oracle 10g.  14-NOV-10 is not between 14-APR-10 and 15-APR-10.  Not trying to argue, but you had me convinced until I tested it.

Open in new window

Sean Stuber

try this

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

then run your query

nole172

Not sure if that request was directed at me, but after altering the sessio, and of course changing the date criteria to conform to the new format:

select * from test where trunc(datefield) between '2010-04-14' and '2010-04-15'
nole172@PROD> /

ONEFIELD DATEFIELD
-------------------- -------------------
Yup 2010-04-14 18:04:11
Yup 2010-04-14 18:04:11
Yup 2010-04-14 18:04:11
Yup 2010-04-14 18:04:11
Yup 2010-04-14 18:04:11
Yup 2010-04-14 18:04:11
Yup 2010-04-14 18:04:11

7 rows selected.