Skip to main content
ilssac
ilssac
Inspiring
November 14, 2011
Answered

Bizare date field behavior in an Oracle database.

  • November 14, 2011
  • 2 replies
  • 1813 views

I have this bizare data problem quering an oracle date field.

Look at these two result sets.

Missing Two records.:

COUNTY_CDCONAMEFILE_DATETRANSMISSIONSRECORDSNO_ERROR_RECORDSERROR_RECORDSTOTAL_ERRORS
03    AMADOR                     07-NOV-11             10                 405                396                9                  9                 
10    FRESNO                     07-NOV-11             1                  204                200                4                  4                 
16    KINGS                      07-NOV-11             3                  1296               1110               186                194               
20    MADERA                     07-NOV-11             1                  552                535                17                 17                
51    SUTTER                     07-NOV-11             1                  43                 5                  38                 38                 

WHERE 
        r.file_date BETWEEN TO_DATE('2011-11-07 00:00:00','YYYY-MM-DD HH24:MI:SS') 
                        AND TO_DATE('2011-11-07 15:00:00','YYYY-MM-DD HH24:MI:SS')

All Expected records

COUNTY_CDCONAMEFILE_DATETRANSMISSIONSRECORDSNO_ERROR_RECORDSERROR_RECORDSTOTAL_RECORDS
03    AMADOR                     07-NOV-11             10                 405                396                9                  9                 
10    FRESNO                     07-NOV-11             1                  204                200                4                  4                 
16    KINGS                      07-NOV-11             3                  1296               1110               186                194               
20    MADERA                     07-NOV-11             1                  552                535                17                 17                
50    STANISLAUS                 07-NOV-11             2                  17002              7165               9837               10028             
51    SUTTER                     07-NOV-11             1                  43                 5                  38                 38                 

WHERE 
        r.file_date BETWEEN TO_DATE('2011-11-07 00:00:00','YYYY-MM-DD HH24:MI:SS') 
                        AND TO_DATE('2011-11-07 15:59:59','YYYY-MM-DD HH24:MI:SS')

Can anybody tell me why looking for recrods by the FILE_DATE field that has no time portion, it is a 'DATE' data type field, would produce different results based on different time parameters passed into the WHERE clause?  This has me really confused and I just can not figure out why this behavior changes which makes me leary to trust a work around solution of just apending random time data to the where clause.

Below it the entire SQL query from the origanal source.

SELECT
r.county_cd,
c.coname,
COUNT(unique r.file_name) AS transmissions,
count(r.use_no) AS records,
count(r.use_no) - count(e.use_no) AS no_error_records,
count(e.use_no) AS error_records, 
sum(e.errors) AS total_errors
FROM
raw_pur r INNER JOIN 
pur_lookup l
ON (r.use_no = l.use_no AND r.year = l.seq_year) LEFT OUTER JOIN
(
SELECT 
year,
use_no,
count(use_no) AS errors
FROM
errors
WHERE
NOT (error_code = 17 OR error_code = 20 OR error_code = 72 OR error_code = 52 OR
(error_code = 12 AND error_type = 'POSSIBLE') OR 
(error_code = 69 AND error_type = 'POSSIBLE') OR 
(error_code = 37 AND error_type = 'POSSIBLE') OR 
(error_code = 39 AND error_type = 'POSSIBLE'))
GROUP BY
year,
use_no
) e ON (r.year = e.year AND r.use_no = e.use_no) INNER JOIN
county c
ON (r.county_cd = c.county_cd)
WHERE 
r.file_date BETWEEN <cfqueryparam value="#form.fromDate#" cfsqltype="cf_sql_date"> 
AND <cfqueryparam value="#form.toDate#" cfsqltype="cf_sql_date">
GROUP BY
r.county_cd,
c.coname
ORDER BY
r.county_cd
This topic has been closed for replies.
Correct answer Owainnorth

Two things to consider: firstly yes, Oracle *always* stores a time portion in a date column, midnight if you don't specify. Secondly cf_sql_date might give you weird results because of this, I tend to use timestamp.

How was the date inputted in the first place? A lot of the time it's put in using SYSDATE, which not only stores the current date but time also, whether or not you can see that in your form is another thing. You therefore might end up not getting results because you're doing something like this:

WHERE itemdate = '08-FEB-2011'

Behind the scenes, itemdate is actually storing a time part as well, so it does *not* equal that date, it's greater than it.

Use TRUNC(itemdate) if you want Oracle to just consider the date part in queries.

2 replies

D
Dan_Bracuk
Inspiring
November 14, 2011

When I do date range queries where the date field includes the time, I don't use between.  I use

where theDateField >= #dateVar1#

and theDateField < #the day after dateVar2#

Regarding what is happening to you, those records probably have values in the time component of file_date but you don't see it because you are getting formatted output. 

Owainnorth
OwainnorthCorrect answer
Inspiring
November 15, 2011

Two things to consider: firstly yes, Oracle *always* stores a time portion in a date column, midnight if you don't specify. Secondly cf_sql_date might give you weird results because of this, I tend to use timestamp.

How was the date inputted in the first place? A lot of the time it's put in using SYSDATE, which not only stores the current date but time also, whether or not you can see that in your form is another thing. You therefore might end up not getting results because you're doing something like this:

WHERE itemdate = '08-FEB-2011'

Behind the scenes, itemdate is actually storing a time part as well, so it does *not* equal that date, it's greater than it.

Use TRUNC(itemdate) if you want Oracle to just consider the date part in queries.

ilssac
ilssacAuthor
Inspiring
November 15, 2011

Thanks guys.  The errant records in question do indeed have time portions to the values in the FILE_DATE field, though they are not supposed to and all the other records do not.

Thanks for the suggestion to use the to_char function to suss out the actual, full values. 

It is always so useful to have helpful IDEs (Oracle SQL Developer in my case) that hide information from you.  I was using this to look at the field expecting to find time data, but was not seeing it.  I guess SQL developer is formatting the date-time value for me since this is a date field.  How helpful in a debugging situation.

Now to figure out why these records are in the database in this way in the first place.  Because they are not supposed to be.

Again, thanks for the help.

Ian

-
-__cfSearching__-
Inspiring
November 14, 2011

I rarely use Oracle, but the online documentation suggests a DATE field can include a time value. ie "This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND". If you apply to_char() with a full date/time mask, do the values contain a time? (I know you said they do not, but it would not hurt to confirm it.)

-Leigh

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices