Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2012
    Posts
    9

    Unanswered: string to date field errors

    Greetings all,

    I have the following queries trying to convert an 8 character substring in the format of 'MMDDYYYY' from 2 particular tables.

    select count(attrval) from mpi_memattr where attrrecno=46 and (to_date(substr(attrval, 16, 8),
    'MM-DD-YYYY HH24:MIS')) between '07/01/2012' and '07/31/2012';

    &

    select count((idnumber)) from mpi_memcustident where attrrecno=36 and (to_date(substr(idnumber, 16, 8),
    'MM-DD-YYYY HH24:MIS')) between '1-Jul-2012' and '31-Jul-2012';

    Here is the DDL for the mpi_memattr table with the ATTRVAL field in bold:

    CREATE TABLE INITIATE_ADMIN.MPI_MEMATTR
    (
    MEMRECNO NUMBER(19) NOT NULL,
    MEMSEQNO NUMBER(5) NOT NULL,
    CAUDRECNO NUMBER(19) NOT NULL,
    MAUDRECNO NUMBER(19) NOT NULL,
    RECSTAT CHAR(1 BYTE) NOT NULL,
    ATTRRECNO NUMBER(5) NOT NULL,
    ASAIDXNO NUMBER(5) NOT NULL,
    ATTRVAL VARCHAR2(128 BYTE) NOT NULL
    )

    Here is the DDL for the mpi_memcustident table with the IDNUMBER field in bold:

    CREATE TABLE INITIATE_ADMIN.MPI_MEMCUSTIDENT
    (
    MEMRECNO NUMBER(19) NOT NULL,
    MEMSEQNO NUMBER(5) NOT NULL,
    CAUDRECNO NUMBER(19) NOT NULL,
    MAUDRECNO NUMBER(19) NOT NULL,
    RECSTAT CHAR(1 BYTE) NOT NULL,
    ATTRRECNO NUMBER(5) NOT NULL,
    ASAIDXNO NUMBER(5) NOT NULL,
    IDNUMBER CHAR(40 BYTE) NOT NULL,
    IDEXPDATE DATE,
    SCORE VARCHAR2(30 BYTE)
    )

    I am receiving the following error message from the 1st query - Gives not a valid month:

    and

    I am also receiving the following error message from the 2nd query - Gives: ORA-01841: (full) year must be between -4713 and +9999, and not be 0.

    I have been whacking my brain for the past 2 days trying to resolve these error messages so I can get the queries to work. Any further help will be appreciated.

    Sincerely,

    Patrick Quinn

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If the data is 'dirty' (not really MMDDYYYY) then you'll get error.
    This is what results when DATE is stored in VARCHAR2 or NUMBER column.

    post proof that every row contains valid DATE in substr(idnumber, 16, 8)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    For the date conversions, your seem to be picking only 8 characters for a date format that requires a minimum of 10 characters to parse. What do you get for the following query:

    Code:
    select substr(attrval, 16, 8)
    from mpi_memattr 
    where attrrecno=46

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •