Solved

Date query not running correctly

Posted on 2007-03-20
3
186 Views
Last Modified: 2010-03-20
Oracle, SQL:
When running this query it is returning all rows,  the column "Initial_scan_date" is a date field.  It is not filtering at all to any dates.  Dates in the column are example:  07/01/2006, 12/21/2006 09:05:26 in both formats.  Any idea's???

select to_date(to_char(INITIAL_SCAN_DATE,'MM/DD/YYYY'), 'MM/DD/YYYY')"Inital_date"
from SEC_ADMIN_JUSTIFICATION
where REC_SOURCE = 'UTS' or REC_SOURCE = 'WIN SERVE'
and to_date(to_char(INITIAL_SCAN_DATE,'MM/DD/YYYY'), 'MM/DD/YYYY') BETWEEN to_date('01/01/2007', 'MM/DD/YYYY')AND sysdate
0
Comment
Question by:knamc
  • 2
3 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
maybe this works better:

select TRUNC(INITIAL_SCAN_DATE) "Inital_date"
from SEC_ADMIN_JUSTIFICATION
where REC_SOURCE = 'UTS' or REC_SOURCE = 'WIN SERVE'
and TRUNC(INITIAL_SCAN_DATE) >= to_date('01/01/2007', 'MM/DD/YYYY')
AND TRUNC(INITIAL_SCAN_DATE) <= sysdate
0
 

Author Comment

by:knamc
Comment Utility
Still returning all dates??? Not filtering??
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
I see now, you have an OR without brackets...

select to_date(to_char(INITIAL_SCAN_DATE,'MM/DD/YYYY'), 'MM/DD/YYYY')"Inital_date"
from SEC_ADMIN_JUSTIFICATION
where ( REC_SOURCE = 'UTS' or REC_SOURCE = 'WIN SERVE' )
and to_date(to_char(INITIAL_SCAN_DATE,'MM/DD/YYYY'), 'MM/DD/YYYY') BETWEEN to_date('01/01/2007', 'MM/DD/YYYY')AND sysdate

alternatively:

select to_date(to_char(INITIAL_SCAN_DATE,'MM/DD/YYYY'), 'MM/DD/YYYY')"Inital_date"
from SEC_ADMIN_JUSTIFICATION
where REC_SOURCE IN ( 'UTS'  ,  'WIN SERVE' )
and to_date(to_char(INITIAL_SCAN_DATE,'MM/DD/YYYY'), 'MM/DD/YYYY') BETWEEN to_date('01/01/2007', 'MM/DD/YYYY')AND sysdate
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now