Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2010
    Posts
    22

    Unanswered: 'literal does not match format string'

    Hey Guys,

    I am trying to create a simple table in Oracle SQL Plus but everytime I try to insert records into the table I get an error saying:
    'ERROR at line 2: ORA-01861: literal does not match format string'

    Here is the table code:

    CREATE TABLE GAME_DATES_AND_VENUES (
    GameDatesAndVenuesID INTEGER NOT NULL,
    Game VARCHAR(50),
    Dates DATE,
    Venues VARCHAR(50)) ;

    Here is the records to be inserted:

    INSERT INTO GAME_DATES_AND_VENUES (GameDatesAndVenuesID, Game, Dates, Venues)
    VALUES ('221', 'Boston Celtics Vs. New York Knicks', '2010-09-01', 'TD Garden');

    I get this error at the DATE datatype by the way.

    Thanks for looking.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    '2010-09-01' is a VARCHAR2 literal. When passed as DATE parameter, it is implicitly converted using NLS_DATE_FORMAT setting (see its actual value in V$NLS_PARAMETERS).

    Suggestion: do not pass string, pass DATE parameter by applying TO_DATE function with corresponding format mask. In your case, it would be 'YYYY-MM-DD' or 'YYYY-DD-MM', depending of date represented by that string (September, 1st or January, 9th?). Study it in SQL User's Guide, available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/

  3. #3
    Join Date
    Aug 2010
    Posts
    22
    FlyBoy,
    I originally had it as 01/09/2010 but the DATE data type specifies that date needs to be (as you quite rightly said) as this: yyyy-mm-dd (ten positions).

    I changed it but this error persisted in appearing each time.
    Im not too sure what you mean by VARCHAR2 as i tried changing DATE to INTEGER but i received another error.

    Thanks

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    '2010-09-01', '01/09/2010', 'September, 1st 2010' are just string representations of one (the same) DATE. DATE data type has no format, anyway the string representation may be converted to it using TO_DATE function with proper mask.
    Instead of making chaotic changes (it is fine you did not post details - just forget them), did you try to use TO_DATE function as suggested in previous post? If no, why? If so, how exactly?

  5. #5
    Join Date
    Aug 2010
    Posts
    22
    taking in what you said in the last post, would this work and do i keep the DATE data type the same?:

    INSERT INTO GAME_DATES_AND_VENUES (GameDatesAndVenuesID, Game, Dates, Venues)
    VALUES ('221', 'Boston Celtics Vs. New York Knicks', to_date('2010-09-01'_date, 'ddmmyyyy') , 'TD Garden');

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ArminM View Post
    taking in what you said in the last post, would this work and do i keep the DATE data type the same?:

    INSERT INTO GAME_DATES_AND_VENUES (GameDatesAndVenuesID, Game, Dates, Venues)
    VALUES ('221', 'Boston Celtics Vs. New York Knicks', to_date('2010-09-01'_date, 'ddmmyyyy') , 'TD Garden');
    No. Why do you not try it?
    Code:
    SQL> select to_date('2010-09-01'_date, 'ddmmyyyy') from dual;
    select to_date('2010-09-01'_date, 'ddmmyyyy') from dual
                               *
    ERROR at line 1:
    ORA-00911: invalid character
    
    SQL> select to_date('2010-09-01', 'ddmmyyyy') from dual;
    select to_date('2010-09-01', 'ddmmyyyy') from dual
                   *
    ERROR at line 1:
    ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    What is the purpose of "_date" after date string representation? It shall not be there at all.
    If '20' is day number, '10' is month number and '-09-' is year number, then it is correct. Unfortunately, the last is wrong, as Oracle complains. Maybe you should use date format corresponding with that string representation (I already posted it).

  7. #7
    Join Date
    Aug 2010
    Posts
    22
    Flyboy,

    I cant say I understand fully your explanation here. For the record I'm trying to create for GAME_DATES_AND_VENUES table is the ID, the actual game, the date the game will take place and the venue.
    The date is 1st September 2010.
    How can i change my INSERT code so i don't get the error.

    Thanks.

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ArminM View Post
    How can i change my INSERT code so i don't get the error.
    What about
    Quote Originally Posted by flyboy
    use date format corresponding with that string representation (I already posted it)
    Even you posted the correct format mask ('YYYY-MM-DD'). Is it so hard to use it?
    Code:
    SQL> alter session set nls_date_format='fmMonth ddth, yyyy hh24:mi:ss';
    
    Session altered.
    
    SQL> select to_date('2010-09-01', 'yyyy-mm-dd') from dual;
    
    TO_DATE('2010-09-01','YYYY-MM
    -----------------------------
    September 1st, 2010 0:0:0
    Note, the NLS_DATE_FORMAT was changed to display DATE in that specific format - no need for you to set it.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    08:03:59 SQL> @a1
    08:04:05 SQL> CREATE TABLE GAME_DATES_AND_VENUES (
    08:04:05   2  GameDatesAndVenuesID INTEGER NOT NULL,
    08:04:05   3  Game VARCHAR(50),
    08:04:05   4  Dates DATE,
    08:04:05   5  Venues VARCHAR(50)) ;
    
    Table created.
    
    08:04:05 SQL> 
    08:04:05 SQL> 
    08:04:05 SQL> INSERT INTO GAME_DATES_AND_VENUES (GameDatesAndVenuesID, Game, Dates, Venues)
    08:04:05   2  VALUES ('221', 'Boston Celtics Vs. New York Knicks', TO_DATE('2010-09-01','YYYY-MM-DD'), 'TD Garden');
    
    1 row created.
    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.

  10. #10
    Join Date
    Aug 2010
    Posts
    22
    I am now trying to create a simple table but when i try to see the records created i get something which i am not expecting:

    Table:

    CREATE TABLE BC_PLAYER_ROSTER (
    PlayerRosterID INTEGER NOT NULL,
    FirstName VARCHAR(50),
    Surname VARCHAR(50),
    DateOfBirth DATE,
    Height VARCHAR(50),
    Weight VARCHAR(50),
    Position VARCHAR(50),
    NationalExperience INTEGER,
    InternationalExperience INTEGER) ;

    Records:

    INSERT INTO BC_PLAYER_ROSTER (PlayerRosterID, FirstName,Surname, DateOfBirth, Height, Weight, Position, NationalExperience, InternationalExperience)
    VALUES('11', 'Ray', 'Allen', TO_DATE('1975-07-20','YYYY-MM-DD'), '6"5', '205 lb', 'SG', '120', '12');

    INSERT INTO BC_PLAYER_ROSTER (PlayerRosterID, FirstName,Surname, DateOfBirth, Height, Weight, Position, NationalExperience, InternationalExperience)
    VALUES('12', 'Paul', 'Pierce', TO_DATE('1977-10-13','YYYY-MM-DD'), '6"7', '235lb', 'SF', '100', '9');


    I simply cant get it to look like a simple table with columns and rows...

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The ANSI format for a DATE literal is like this:

    DATE '2010-09-01'

    e.g.
    Code:
    INSERT INTO GAME_DATES_AND_VENUES (GameDatesAndVenuesID, Game, Dates, Venues)
    VALUES ('221', 'Boston Celtics Vs. New York Knicks', DATE '2010-09-01', 'TD Garden');

  12. #12
    Join Date
    Aug 2010
    Posts
    22
    whenever i create the table and row below i am presented with a strange looking set of columns and rows?

    Table:
    CREATE TABLE BC_PLAYER_ROSTER (
    PlayerRosterID INTEGER NOT NULL,
    FirstName VARCHAR(50),
    Surname VARCHAR(50),
    DateOfBirth DATE,
    Height VARCHAR(50),
    Weight VARCHAR(50),
    Position VARCHAR(50),
    NationalExperience INTEGER,
    InternationalExperience INTEGER) ;

    Row:
    INSERT INTO BC_PLAYER_ROSTER (PlayerRosterID, FirstName,Surname, DateOfBirth, Height, Weight, Position, NationalExperience, InternationalExperience)
    VALUES('11', 'Ray', 'Allen', TO_DATE('1975-07-20','YYYY-MM-DD'), '6"5', '205 lb', 'SG', '120', '12');

    Click image for larger version. 

Name:	select statement.png 
Views:	119 
Size:	26.5 KB 
ID:	11072


    Im not sure if i have done something different to cause this OR is this how oracle displays tables???

    thanks

  13. #13
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ArminM View Post
    Im not sure if i have done something different to cause this OR is this how oracle displays tables???
    Just to be accurate, you are using sqlplus. Maybe you should read about it in SQL*Plus User's Guide and Reference (just another documentation book). Setting of some parameters may "improve" the look a bit:
    Code:
    SET LINESIZE <the width of the screen>
    SET PAGESIZE 100 -- or any quite large number
    COLUMN height FORMAT a10 -- the display width of the column
    COLUMN <the same for other "wide" columns>
    Did you consider using some GUI tool, e.g. SQL Developer (also provided by Oracle)?

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >FirstName VARCHAR(50),

    why using VARCHAR & not using VARCHAR2?
    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.

  15. #15
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by anacedent View Post
    >FirstName VARCHAR(50),

    why using VARCHAR & not using VARCHAR2?
    There is no difference between VARCHAR and VARCHAR2 (actually VARCHAR gets converted to VARCHAR2 silently)

Posting Permissions

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