mySQL question - GoFuckYourself.com

Caspah · 11-03-2003, 07:26 PM

i have a question for anyone that knows sql cuz im a noob...

SQL> INSERT INTO IS_QUALIFIED
2 VALUES (1234, 'ISM 1234','1988-09-09');
VALUES (1234, 'ISM 1234','1988-09-09')
*
ERROR at line 2:
ORA-01861: literal does not match format string

How can i fix this error with inserting the date as a value?
I appreciate any help anyone can give.

icedemon · 11-03-2003, 07:40 PM

What is that "2" on the 2nd line. It should be something like this

INSERT INTO IS_QUALIFIED VALUES (1234, 'ISM 1234','1988-09-09');

I hope your not using spaces in your table names.

Caspah · 11-03-2003, 08:02 PM

If you mean by spaces in my tables as the IS_QUALIFIED...spaces where the underscore is? No, I have underscores. That 2 was there because i pressed enter and did it on the second line. How would I format the date so I can just use a month and year. Every time I try it like that it says the wrong format. Thanks for whatever info someone can let me know.

NetRodent · 11-03-2003, 08:09 PM

I'm guessing your table is set to the "date" column expects a "date". If you want to do just years and months either change it to a "char" column. You may be better of keeping the date format but just ignoring the day (always use 01).

Lane · 11-03-2003, 08:11 PM

switch to mysql

what u're using is Oracle ?

TEK9 · 11-03-2003, 08:11 PM

Quote:

Originally posted by Caspah
i have a question for anyone that knows sql cuz im a noob...

SQL> INSERT INTO IS_QUALIFIED
2 VALUES (1234, 'ISM 1234','1988-09-09');
VALUES (1234, 'ISM 1234','1988-09-09')
*
ERROR at line 2:
ORA-01861: literal does not match format string

How can i fix this error with inserting the date as a value?
I appreciate any help anyone can give.

the problem is you haven't told it what columns to put the values in. It should look like this:

INSERT INTO IS_QUALIFIED (name, of, each, column)
VALUES (1234, 'ISM 1234','1988-09-09');

the names of each column should come in the first set of parentheses (don't forget the commas), and then the proper values in the SAME ORDER come in the second set of parentheses.

sweet7 · 11-03-2003, 08:15 PM

Tek 9 has a point but it doesn't need to be specified unless you're re-arranging the order of the fields or leaving some fields out altogether.

Do what netrodent said if you want the date but not necessarily in the mysql format then use regular char types for your fields.

A little reading never hurt anybody

Caspah · 11-03-2003, 08:41 PM

Still getting the same error...

Table command:

CREATE TABLE IS_QUALIFIED
(FACULTY_ID INTEGER NOT NULL,
COURSE_ID VARCHAR2(8) NOT NULL,
DATE_QUALIFIED VARCHAR2(10) NOT NULL,
CONSTRAINT IS_QUALIFIED_PK PRIMARY KEY (FACULTY_ID, COURSE_ID),
CONSTRAINT IS_QUALIFIED_FK FOREIGN KEY (FACULTY_ID) REFERENCES FACULTY (FACULTY_ID),
CONSTRAINT IS_QUALIFIED_FK2 FOREIGN KEY (COURSE_ID) REFERENCES COURSE (COURSE_ID));

Load command:

INSERT INTO IS_QUALIFIED
VALUES (2143, 'ISM 3112', '1988-09-01');
INSERT INTO IS_QUALIFIED
VALUES (2143, 'ISM 3113', '1988-09-01');
INSERT INTO IS_QUALIFIED
VALUES (3487, 'ISM 4212', '1995-09-01');
INSERT INTO IS_QUALIFIED
VALUES (3487, 'ISM 4930', '1996-09-01');
INSERT INTO IS_QUALIFIED
VALUES (4756, 'ISM 3113', '1991-09-01');
INSERT INTO IS_QUALIFIED
VALUES (4756, 'ISM 3112', '1991-09-01');

they are all in the correct order also..

TEK9 · 11-03-2003, 08:50 PM

you're never gonna get anywhere until you tell it what columns to put the values in

Caspah · 11-03-2003, 09:09 PM

INSERT INTO IS_QUALIFIED(FACULTY_ID, COURSE_ID, DATE_QUALIFIED)
VALUES (2143, 'ISM 3112', '1988-09-01');

^^^i tried that also and it still didnt work.

TEK9 · 11-03-2003, 09:30 PM

i dunno then. you might want to change the column for the date back into the DATE format, and use the TO_DATE command when you format the value:

TO_DATE('1988-09-01', 'yyyy-Mon-dd')

Caspah · 11-03-2003, 09:52 PM

Thanks for the help guys

11-03-2003, 07:26 PM	#1
Caspah Confirmed User Join Date: Oct 2003 Posts: 251	mySQL question i have a question for anyone that knows sql cuz im a noob... SQL> INSERT INTO IS_QUALIFIED 2 VALUES (1234, 'ISM 1234','1988-09-09'); VALUES (1234, 'ISM 1234','1988-09-09') * ERROR at line 2: ORA-01861: literal does not match format string How can i fix this error with inserting the date as a value? I appreciate any help anyone can give. __________________ FreeTicketCash - Everyone likes to signup for a free ticket, wide variety of free e-mail sites! DatingGold - 1$ per email dating site, also per signup.

11-03-2003, 07:40 PM	#2
icedemon Confirmed User Join Date: Jun 2003 Location: Lutz, FL Posts: 1,022	What is that "2" on the 2nd line. It should be something like this INSERT INTO IS_QUALIFIED VALUES (1234, 'ISM 1234','1988-09-09'); I hope your not using spaces in your table names. __________________ Clips4Sale.com

11-03-2003, 08:02 PM	#3
Caspah Confirmed User Join Date: Oct 2003 Posts: 251	If you mean by spaces in my tables as the IS_QUALIFIED...spaces where the underscore is? No, I have underscores. That 2 was there because i pressed enter and did it on the second line. How would I format the date so I can just use a month and year. Every time I try it like that it says the wrong format. Thanks for whatever info someone can let me know. __________________ FreeTicketCash - Everyone likes to signup for a free ticket, wide variety of free e-mail sites! DatingGold - 1$ per email dating site, also per signup.

11-03-2003, 08:09 PM	#4
NetRodent Confirmed User Join Date: Jan 2002 Location: In the walls of your house. Posts: 3,985	I'm guessing your table is set to the "date" column expects a "date". If you want to do just years and months either change it to a "char" column. You may be better of keeping the date format but just ignoring the day (always use 01). __________________ "Every normal man must be tempted, at times, to spit on his hands, hoist the black flag, and begin slitting throats." --H.L. Mencken

11-03-2003, 08:11 PM	#5
Lane Will code for food... Join Date: Apr 2001 Location: Buckeye, AZ Posts: 8,496	switch to mysql what u're using is Oracle ? __________________

11-03-2003, 08:15 PM	#7
sweet7 Confirmed User Join Date: May 2003 Posts: 1,792	Tek 9 has a point but it doesn't need to be specified unless you're re-arranging the order of the fields or leaving some fields out altogether. Do what netrodent said if you want the date but not necessarily in the mysql format then use regular char types for your fields. A little reading never hurt anybody __________________ ICQ: 282814268

11-03-2003, 08:41 PM	#8
Caspah Confirmed User Join Date: Oct 2003 Posts: 251	Still getting the same error... Table command: CREATE TABLE IS_QUALIFIED (FACULTY_ID INTEGER NOT NULL, COURSE_ID VARCHAR2(8) NOT NULL, DATE_QUALIFIED VARCHAR2(10) NOT NULL, CONSTRAINT IS_QUALIFIED_PK PRIMARY KEY (FACULTY_ID, COURSE_ID), CONSTRAINT IS_QUALIFIED_FK FOREIGN KEY (FACULTY_ID) REFERENCES FACULTY (FACULTY_ID), CONSTRAINT IS_QUALIFIED_FK2 FOREIGN KEY (COURSE_ID) REFERENCES COURSE (COURSE_ID)); Load command: INSERT INTO IS_QUALIFIED VALUES (2143, 'ISM 3112', '1988-09-01'); INSERT INTO IS_QUALIFIED VALUES (2143, 'ISM 3113', '1988-09-01'); INSERT INTO IS_QUALIFIED VALUES (3487, 'ISM 4212', '1995-09-01'); INSERT INTO IS_QUALIFIED VALUES (3487, 'ISM 4930', '1996-09-01'); INSERT INTO IS_QUALIFIED VALUES (4756, 'ISM 3113', '1991-09-01'); INSERT INTO IS_QUALIFIED VALUES (4756, 'ISM 3112', '1991-09-01'); they are all in the correct order also.. __________________ FreeTicketCash - Everyone likes to signup for a free ticket, wide variety of free e-mail sites! DatingGold - 1$ per email dating site, also per signup.

11-03-2003, 08:50 PM	#9
TEK9 Registered User Join Date: Nov 2003 Posts: 3	you're never gonna get anywhere until you tell it what columns to put the values in

11-03-2003, 09:09 PM	#10
Caspah Confirmed User Join Date: Oct 2003 Posts: 251	INSERT INTO IS_QUALIFIED(FACULTY_ID, COURSE_ID, DATE_QUALIFIED) VALUES (2143, 'ISM 3112', '1988-09-01'); ^^^i tried that also and it still didnt work. __________________ FreeTicketCash - Everyone likes to signup for a free ticket, wide variety of free e-mail sites! DatingGold - 1$ per email dating site, also per signup.

11-03-2003, 09:30 PM	#11
TEK9 Registered User Join Date: Nov 2003 Posts: 3	i dunno then. you might want to change the column for the date back into the DATE format, and use the TO_DATE command when you format the value: TO_DATE('1988-09-01', 'yyyy-Mon-dd')

11-03-2003, 09:52 PM	#12
Caspah Confirmed User Join Date: Oct 2003 Posts: 251	Thanks for the help guys __________________ FreeTicketCash - Everyone likes to signup for a free ticket, wide variety of free e-mail sites! DatingGold - 1$ per email dating site, also per signup.