7 Replies Latest reply: Nov 15, 2011 9:45 AM by -==cfSearching==- RSS

    Bizare date field behavior in an Oracle database.

    ilssac Community Member
    ilssac Nov 14, 2011 2:04 PM

      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
      • 1175 Views
      • Tags:
        • 1. Re: Bizare date field behavior in an Oracle database.
          -==cfSearching==- Community Member
          -==cfSearching==- Nov 14, 2011 2:19 PM (in response to ilssac)

          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

          • 2. Re: Bizare date field behavior in an Oracle database.
            Dan Bracuk Community Member
            Dan Bracuk Nov 14, 2011 2:46 PM (in response to ilssac)

            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. 

            • 3. Re: Bizare date field behavior in an Oracle database.
              Owain North Community Member
              Owain North Nov 15, 2011 2:48 AM (in response to Dan Bracuk)

              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.

              • 4. Re: Bizare date field behavior in an Oracle database.
                ilssac Community Member
                ilssac Nov 15, 2011 6:48 AM (in response to Owain North)

                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

                • 5. Re: Bizare date field behavior in an Oracle database.
                  -==cfSearching==- Community Member
                  -==cfSearching==- Nov 15, 2011 6:35 AM (in response to Owain North)

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

                   

                  Keep in mind using a function on a column often inhibits the use of indexes resulting in a full index or table scan. I typically use Dan's approach because it is a little more index friendly.

                   

                  -Leigh

                  • 6. Re: Bizare date field behavior in an Oracle database.
                    Owain North Community Member
                    Owain North Nov 15, 2011 9:05 AM (in response to -==cfSearching==-)

                    Unless you're using a function-based index of course, in which case the reverse is true

                     

                    I have a login.sql set up on my SQLPlus now which makes DATEs show as date/time by default to help debug exactly this kind of issue.

                     

                    ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

                    • 7. Re: Bizare date field behavior in an Oracle database.
                      -==cfSearching==- Community Member
                      -==cfSearching==- Nov 15, 2011 9:45 AM (in response to Owain North)

                      Unless you're using a function-based index of course, in which case the reverse is true

                       

                       

                      Yep. There is always an exception to every rule. Though my impression was non-function based indexes are more common. But you are right, the correct answer is ... "it depends"

                       

                       

                      -Leigh