locked
Getting an error msg when trying to create row in table RRS feed

  • Question

  • INSERT

     

    INTO [employees] ([LastName], [FirstName], [Address], [City], [State], [TelephoneAreaCode], [TelephoneNumber], [HireDate], [Salary], [Gender], [Age], [JobTitle])

    VALUES

     

    ('Edelman','Glenn','175 Bishop Lane','La Jolla','CA','619','555-0199','2003-10-07','10.75','M','64','Cashier')


    WHen i execute this i am getting the error

    Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting date and/or time from character string.


    i have no clue why i am getting this error. Below is the Table i was INSERTing the data into


    CREATE TABLE employees (
    EmployeeID int identity Primary Key,
    LastName nvarchar,
    FirstName nvarchar,
    Address nvarchar(50),
    City NVARCHAR(120),
    State NvarChar(40),
    TelephoneAreaCode NVarChar(50),
    TelephoneNumber nvarchar(20),
    HireDate date,
    Salary money,
    Gender nvarchar(32),
    Age int,
    JobTitle nvarchar);

    GO

    Sunday, January 31, 2010 7:23 PM

Answers

  • The following should work:

    INSERT INTO [employees] ([LastName], [FirstName], [Address], [City], [State], [TelephoneAreaCode], [TelephoneNumber], [HireDate], [Salary], [Gender], [Age], [JobTitle])
    VALUES ('Slentz','Raj','123 Torrey Dr','North Clairmont','CA','619','555-0123','2000-06-01',7.75,'M',34,'Assistant Manager')
    

    Kalman Toth, SQL Server 2008 & BI Training, OLAP, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by porginski Monday, February 1, 2010 3:06 PM
    Monday, February 1, 2010 7:42 AM

All replies

  • I put in some more rows but still only highlighting two to be executed

    INSERT

     

    INTO [employees] ([LastName], [FirstName], [Address], [City], [State], [TelephoneAreaCode], [TelephoneNumber], [HireDate], [Salary], [Gender], [Age], [JobTitle])

    VALUES

     

    ('Edelman','Glenn','175 Bishop Lane','La Jolla','CA','619','555-0199','2003-10-07','10.75','M','64','Cashier')

     

     

    When i run that alone i am getting the error

    Msg 8152, Level 16, State 4, Line 1

    String or binary data would be truncated.

    The statement has been terminated.

    Sunday, January 31, 2010 7:54 PM
  • You are missing the length for 3 nvarchar columns.

    Notes:

    1. For numeric literals you don't have to use (') single quote
    2. Some of the columns appear to be oversized like Gender - CHAR(1) would fit

    CREATE TABLE employees (
    EmployeeID int identity Primary Key,
    LastName nvarchar(30),
    FirstName nvarchar(30),
    Address nvarchar(50),
    City NVARCHAR(120),
    State NvarChar(40),
    TelephoneAreaCode NVarChar(50),
    TelephoneNumber nvarchar(20),
    HireDate date,
    Salary money,
    Gender nvarchar(32),
    Age int,
    JobTitle nvarchar(40));
    
    
    
    INSERT INTO [employees] ([LastName], [FirstName], [Address], [City], [State], [TelephoneAreaCode], [TelephoneNumber], [HireDate], [Salary], [Gender], [Age], [JobTitle])
    VALUES ('Edelman','Glenn','175 Bishop Lane','La Jolla','CA','619','555-0199','2003-10-07',10.75,'M',64,'Cashier') 
    

    Kalman Toth, SQL Server 2008 & BI Training, OLAP, SSIS, SSRS; http://www.SQLUSA.com
    Monday, February 1, 2010 12:22 AM
  • alright most of them are working but some reason this below keep giving me the same error i was getting above the truncated one

    INSERT

     

    INTO [employees] ([LastName], [FirstName], [Address], [City], [State], [TelephoneAreaCode], [TelephoneNumber], [HireDate][Salary[Gender], [Age], [JobTitle])
    VALUES ('Slentz','Raj','123 Torrey Dr','North Clairmont','CA','619','555-0123','2000-06-01','7.75','M','34','Assistant Manager')

    and here is how i changed all this like you said some are too long

    CREATE

     

    TABLE employees (
    EmployeeID int identity Primary Key,
    LastName nvarchar(10),
    FirstName nvarchar(10),
    Address nvarchar(40),
    City NVARCHAR(20),
    State NvarChar(3),
    TelephoneAreaCode NVarChar(4),
    TelephoneNumber nvarchar(10),
    HireDate date,
    Salary money,
    Gender nvarchar(1),
    Age int,
    JobTitle nvarchar(60));

    GO

    Monday, February 1, 2010 6:38 AM
  • The following should work:

    INSERT INTO [employees] ([LastName], [FirstName], [Address], [City], [State], [TelephoneAreaCode], [TelephoneNumber], [HireDate], [Salary], [Gender], [Age], [JobTitle])
    VALUES ('Slentz','Raj','123 Torrey Dr','North Clairmont','CA','619','555-0123','2000-06-01',7.75,'M',34,'Assistant Manager')
    

    Kalman Toth, SQL Server 2008 & BI Training, OLAP, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by porginski Monday, February 1, 2010 3:06 PM
    Monday, February 1, 2010 7:42 AM
  • using this query will working fine
    and why you using NVARCHAR data type ? try to use VARCHAR

    INSERT

    INTO [employees] (

    [LastName]

    ,

    [FirstName]

    ,

    [Address]

    ,

    [City]

    ,

    [State]

    ,

    [TelephoneAreaCode]

    ,

    [TelephoneNumber]

    ,

    [HireDate]

    ,[Salary],

    [Gender]

    ,

    [Age]

    ,

    [JobTitle]

    )

    VALUES

    (

    'Slentz'

    ,

    'Raj'

    ,

    '123 Torrey Dr'

    ,

    'North Clairmont'

    ,

    'CA'

    ,

    '619'

    ,

    '555-0123'

    ,

    '2000-06-01'

    ,

    '7.75'

    ,

    'M'

    ,

    '34'

    ,

    'Assistant Manager'

    )


    --Manigandan-DBA , Mark as Answer if it helps!
    --sqlserver-manigandan.blogspot.com

    Monday, February 1, 2010 11:25 AM
  • this worked now with what you put but i dont understand what is different not sure why it worked can you explain what was the problem so i know next time
    Monday, February 1, 2010 3:06 PM
  • As previously stated, you don't wrap int, doubles, numerics, etc with quotes.  You're query had '34' and '7.75' wrapped in quotes while Kalmans did not. 
    Please mark answered posts. Thanks for your time.
    Thursday, February 11, 2010 2:26 PM
    Answerer