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 AgnihotriAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kamal AgnihotriAuthor Commented:
My query is not giving the expected result. From the query above, I am getting data range outside of what I specified.
0
sdstuberCommented:
that's because you're doing string comparisons instead of date comparisons
0
sdstuberCommented:
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')
AND Username = 'XYZ'
ORDER BY timestamp;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

nole172Commented:
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;

0
sdstuberCommented:
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


0
sdstuberCommented:
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

0
nole172Commented:
My mistake, sdstuber.  Good catch.
0
sdstuberCommented:
glad I could help, both of you!  :)
0
nole172Commented:
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

0
sdstuberCommented:
try  this


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


then run your query
0
nole172Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.