Inserting Dates into Table

Dev Shed Forums Sponsor:

March 10th, 2012, 03:26 PM

Daniel M.

Registered User

Join Date: Mar 2012

Posts: 11

Time spent in forums: 8 h 39 m 48 sec
Reputation 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.

March 10th, 2012, 04:20 PM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,685

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 44 m 28 sec
Reputation Power: 284

Quote:

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.

March 10th, 2012, 06:17 PM

Daniel M.

Registered User

Join Date: Mar 2012

Posts: 11

Time spent in forums: 8 h 39 m 48 sec
Reputation 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

March 11th, 2012, 03:18 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,685

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 44 m 28 sec
Reputation Power: 284

Quote:

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.

March 11th, 2012, 09:21 AM

Daniel M.

Registered User

Join Date: Mar 2012

Posts: 11

Time spent in forums: 8 h 39 m 48 sec
Reputation Power: 0

I understand what you mean now, thank you. Also, thank you for the programming style tips.

Inserting Dates into Table

Developer Shed Advertisers and Affiliates