July 30th, 2012, 06:28 PM
[Solved] Microsoft SQL Server - Conversion Failed When Converting Dates
Hello everyone. I'm currently taking an Intro to Database Systems and am attempting to populate tables as apart of my lab.
The following was used to create the table:
CREATE TABLE customers(
customer_id int IDENTITY(1,1) NOT NULL,
membership_yn char(1) NOT NULL,
membership_number int NOT NULL,
date_became_member date NOT NULL,
customer_first_name varchar(32) NOT NULL,
customer_last_name varchar(32) NOT NULL,
customer_address varchar (128) NOT NULL,
customer_phone varchar(9) NOT NULL,
customer_email varchar(32) NOT NULL,
customer_dob date NOT NULL);
When I run:
INSERT INTO customers VALUES ('Y',1,'07302012','John','Smith','1235 Main Street, Dunmore, PA 18512','1234567','firstname.lastname@example.org','07301980');
I recieve this:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
I'm not sure where to go from here. Any help would be greatly appreciated.
July 30th, 2012, 07:28 PM
you forgot the list of columns
INTO customers ( list,of,columns )
VALUES ( list,of,values )
July 30th, 2012, 07:41 PM
I've added the list of columns as well via another Q&A site.
INSERT INTO customers (member_yn,membership_number,date_became_member,customer_first_name,customer_last_name,customer_addr ess,customer_phone,customer_email,customer_dob) VALUES etc etc
It returned the same error as having no columns identified.
July 30th, 2012, 07:57 PM
Originally Posted by eclypz00
the first query (without the list of columns) was trying to insert 'Y' into customer_id, 1 into membership_yn, '07302012' into membership_number, 'John' into date_became_member, and so on
so by sheer coincidence it was a date value that triggered the first error
now it looks like you have your values lined up with the proper columns, and again it's a date value that's triggering an error
which is the first date value that it encounters? what do you suppose might be wrong with that date format?
July 30th, 2012, 08:18 PM
I looked at your questions and tried again with the recommendation of listing the columns, as well as altering my date format to YYYY-MM-DD and it worked flawlessly.
Originally Posted by r937
I'm still rather confused on everything that has happened but I can say it works now and thank you very much for your help.
Here is my new input:
INSERT INTO customers(membership_yn,membership_number,date_became_member,customer_first_name,customer_last_name, customer_address,customer_phone,customer_email,customer_dob) VALUES ('Y',1,'2012-07-08','John','Smith','TempAddress','1234567890','email@example.com','1980-01-07');
August 31st, 2012, 03:53 AM
Your first mistake was not explicitly listing the columns that you wanted your data to go into. By default, as explained by others, the data will try to be inserted into the table from the first column et al.
Your second mistake was the format of your date. It needs to be in the format 'yyyy-mm-dd'. I also add times otherwise the time will automatically be set at the time the data was entered. I therefore use the format 'yyyy-mm-dd hh:mm'.
In your example I would have entered '2012-07-08 00:00'
August 31st, 2012, 06:20 AM
this is incorrect
Originally Posted by Fear Naught
August 31st, 2012, 06:29 AM
You are quite right. Many apologies for the error. However I still think it would be good practice to be explicit about times.
Originally Posted by r937