#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    11
    Rep Power
    0

    Inserting Dates into Table


    My homework requires me to create a booking table for a hotel and I have created the table but I'm having trouble inserting the dates.

    This is my table:
    Code:
    DROP TABLE BookingDM CASCADE CONSTRAINTS PURGE; 
    CREATE TABLE BookingDM ( 
    hNo NUMBER(3), 
    gNo NUMBER(5), 
    dFrom DATE NOT NULL, 
    dTo DATE NULL, 
    rNo VARCHAR(4), 
    CONSTRAINT BookingDM_PK PRIMARY KEY (hNo, gNo, dFrom), 
    CONSTRAINT BookingDM_FK FOREIGN KEY (hNo, rNo) 
                          REFERENCES RoomDM (hNo, rNo) ON DELETE CASCADE, 
    CONSTRAINT BokingDM_gNo_FK FOREIGN KEY (gNo) 
                          REFERENCES GuestDM (gNo) ON DELETE SET NULL 
    );
    This is the first set I'm attempting to insert
    hNo = 148
    gNo = 11169
    dFrom = 09/03/2009
    dTo = 09/10/2009
    rNo = 202

    This is my attempt to insert the set:
    Code:
    SQL> INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202'); 
    
    INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202') 
                                                 * 
    ERROR at line 1: 
    ORA-01843: not a valid month
    I need my dates to be in the format "MM/DD/YYYY" thank you.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Originally Posted by Daniel M.
    I need my dates to be in the format "MM/DD/YYYY" thank you.
    No, you don't.

    A DATE column does not have a "format". The format is only relevant when displaying the value of such a column.

    To supply a value for a DATE column you can either use the to_date() function or a standard ANSI date literal.

    e.g.: DATE '2009-09-10' specifies September 10th, 2009
    An ANSI date literal is always in ISO format (YYYY-MM-DD)

    With the to_date() function you can specify the input format:

    to_date('09/10/2009', 'MM/DD/YYYY');

    But the input format is completely irrelevant for the display format of the value. Once you have stored a DATE vlaue, you can format any way you like during retrieval using the to_char() function.

    Please see the manual for more details:

    to_char(): http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions200.htm#i1009324

    to_date(): http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions203.htm#i1003589

    You should always specify a format mask when using the to_date() function or use ANSI literals (which have a defined format).
    Never rely on implicit type casts.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    11
    Rep Power
    0
    I'm sorry I did not explain myself well, I was talking about output format not for the input. I have tried the TO_CHAR and TO_DATE but I keep getting not a valid month error.

    Code:
    SQL> INSERT INTO BookingDM VALUES('148', '11169', TO_CHAR(TO_DATE('09/03/2009', 'MM/DD/YYYY'), 'MM/D D/YYYY'), TO_CHAR(TO_DATE('09/10/2009', 'MM/DD/YYYY'), 'MM/DD/YYYY'), '202'); 
    
    INSERT INTO BookingDM VALUES('148', '11169', TO_CHAR(TO_DATE('09/03/2009', 'MM/DD/YYYY'), 'MM/DD/YYY 
                                                 * 
    ERROR at line 1: 
    ORA-01843: not a valid month
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Originally Posted by Daniel M.
    I'm sorry I did not explain myself well, I was talking about output format not for the input. I have tried the TO_CHAR and TO_DATE but I keep getting not a valid month error.
    When you INSERT data, you don't need an "output" format because you are "inputting" values.

    You want to put a value into a DATE column, so use to_date() to create such a value from an input string. There is no "output" involved when you insert data.

    to_char() converts a DATE value back into a string. So there is no need to apply to_char() to a value that you obtained from to_date().

    And it's good coding style to always specify the column list in the INSERT part:
    Code:
    INSERT INTO BookingDM 
    (
      hNo,
      gNo,
      dFrom,
      dTo,
      rNo
    )
    VALUES 
    ( 
      148, 
      11169, 
      TO_DATE('09/03/2009', 'MM/DD/YYYY'), 
      TO_DATE('09/10/2009', 'MM/DD/YYYY'), 
      '202'
    );
    And leave out the single quotes around numbers.
    Single quotes are only used to identify string values, never for numbers.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    11
    Rep Power
    0
    I understand what you mean now, thank you. Also, thank you for the programming style tips.

IMN logo majestic logo threadwatch logo seochat tools logo