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?
 
sdstuberConnect With a Mentor Commented:
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.