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

    Join Date
    Jul 2012
    Posts
    3
    Rep Power
    0

    [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','jsmith@hotmail.com','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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,143
    Rep Power
    4274
    you forgot the list of columns

    INSERT
    INTO customers ( list,of,columns )
    VALUES ( list,of,values )

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    3
    Rep Power
    0
    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.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,143
    Rep Power
    4274
    Originally Posted by eclypz00
    It returned the same error as having no columns identified.
    astonishing

    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    which is the first date value that it encounters? what do you suppose might be wrong with that date format?
    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.

    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','jsmith@hotmail.com','1980-01-07');
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Folkestone, Kent, UK
    Posts
    3
    Rep Power
    0
    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'

    HTH.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,143
    Rep Power
    4274
    Originally Posted by Fear Naught
    I also add times otherwise the time will automatically be set at the time the data was entered.
    this is incorrect
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Folkestone, Kent, UK
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    this is incorrect
    You are quite right. Many apologies for the error. However I still think it would be good practice to be explicit about times.

IMN logo majestic logo threadwatch logo seochat tools logo