1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Comparing Time with Fractional Secs: Error: ORA-01821: date format not recognized

Discussion in 'SQL PL/SQL' started by OldSchoolCoder, May 8, 2014.

  1. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    I have been struggling with comparing a date/time stamp column that includes Fractional Seconds. It is resulting in ORA-01821: date format not recognized. Ultimately, the goal is to get all the Paid Dates of 5/6/2014, grouped by the source_CD and to count the number of claims within each hour. Therefore, the results would look like this:

    Source_CD 1 between 8 and 8:59:59 PM: 3
    Source_CD 1 between 7 and 7:59:59 AM: 1
    Source_CD 3 between 4 and 4:59:59 AM: 4
    Source_CD 3 between 2 and 2:59:59 AM: 2

    The relevent data looks like this:
    SOURCE_CD PAID_DT TIMESTAMP REC_ID
    1 5/6/2014 5/6/2014 8:02:18.429000 PM 1
    1 5/6/2014 5/6/2014 8:52:18.429000 PM 2
    1 5/6/2014 5/6/2014 8:19:18.429000 PM 3
    1 5/6/2014 5/7/2014 7:34:14.247000 AM 4
    2 5/5/2014 5/6/2014 7:24:13.237000 AM 5
    2 1/5/2014 2/30/2014 7:24:13.237000 AM 6
    3 5/6/2014 5/30/2014 4:29:24.141000 AM 7
    3 5/6/2014 5/30/2014 4:09:24.141000 AM 8
    3 5/6/2014 5/30/2014 4:35:24.141000 AM 9
    3 5/6/2014 5/30/2014 4:59:24.141000 AM 10
    3 5/6/2014 5/7/2014 2:21:25.133000 AM 11
    3 5/6/2014 5/7/2014 2:36:25.133000 AM 12
    3 3/9/2014 3/30/2014 6:31:43.321000 AM 13
    3 5/4/2014 5/4/2014 7:34:14.247000 AM 14

    My Query looks like this right now (does not include grouping the timestamps). I want to learn how to query around the Fractional Seconds:
    Code (SQL):
    SELECT SOURCE_CD, COUNT (REC_ID)
    FROM PAID_TABLE
    WHERE PAID_DT = TO_DATE ('05/06/2014','MM/DD/YYYY')
      AND TO_DATE(TIMESTAMP, 'HH:MI:SS.FF PM') >=
          TO_DATE('08:00:00.000000 PM', 'HH:MI:SS.FF PM')
      AND TO_DATE(TIMESTAMP, 'HH:MI:SS.FF PM') <=
          TO_DATE('08:59:59.000000 PM', 'HH:MI:SS.FF PM')
    GROUP BY SOURCE_CD;
    Thanks for any insight on working with Fractional Seconds.
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Re: Comparing Time with Fractional Secs: Error: ORA-01821: date format not recogniz

    Hi,

    TO_DATE(TIMESTAMP, 'HH:MI:SS.FF PM') from PAID_TABLE

    This is giving that error...
     
  3. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Re: Comparing Time with Fractional Secs: Error: ORA-01821: date format not recogniz

    Thanks for your response, but how can I write it to eliminate the error?
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Re: Comparing Time with Fractional Secs: Error: ORA-01821: date format not recogniz

    I too don't know,

    But try with to_char.

    I thought that to_date return only date it will not take hours, seconds...
     
  5. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Re: Comparing Time with Fractional Secs: Error: ORA-01821: date format not recogniz

    That was a great suggestion, but since I have alphanumeric data in the string, it gives ORA-01722: invalid number. Can I possibly first convert the data to military time (example 6pm would be 1800) so I could remove the AM/PM?
     
  6. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Re: Comparing Time with Fractional Secs: Error: ORA-01821: date format not recogniz

    I think I've got it. Since the Timestamp column contains the date as well as the time, I had to use TO_CHAR on it and not on the data I was comparing it to. So it now looks like this and does not receive the error! Yay! Thanks for helping me eventually figure this out!
    Code (SQL):
    SELECT SOURCE_CD, COUNT (REC_ID)
    FROM PAID_TABLE
    WHERE PAID_DT = TO_DATE ('05/06/2014','MM/DD/YYYY')
      AND TO_CHAR(TIMESTAMP, 'HH:MI:SS.FF6 PM') >= '08:00:00.000000 PM'
      AND TO_CHAR(TIMESTAMP, 'HH:MI:SS.FF6 PM') <= '08:59:59.000000 PM'
    GROUP BY SOURCE_CD;
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Re: Comparing Time with Fractional Secs: Error: ORA-01821: date format not recogniz

    Ok Good Man....