?
Solved

Date query not running correctly

Posted on 2007-03-20
3
Medium Priority
?
211 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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18758639
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
ID: 18758648
Still returning all dates??? Not filtering??
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 18758764
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup
Suggested Courses

616 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