Conversion failed when converting date and/or time from character string. What could be wrong?

Question

INSERT INTO dbo.SaleNew(ENQ_AID,DOCKET_NO,SALE_TYPE,VEHICLE_MODEL,SALE_DATE,BOOKING_DATE,DELIVERY_DATE,
                    DEALER_NAME,ENQ_GEN_BY,EXEC_NAME,USER_CR,DATE_CR) SELECT '6','2','0','TEST','2016-05-01','2016-05-10','2016-05-15',
   'ABC','S I','V B','1',SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')

What could be wrong with this query?

Also tried:

INSERT INTO dbo.SaleNew(ENQ_AID,DOCKET_NO,SALE_TYPE,VEHICLE_MODEL,SALE_DATE,BOOKING_DATE,DELIVERY_DATE,
                    DEALER_NAME,ENQ_GEN_BY,EXEC_NAME,USER_CR,DATE_CR) SELECT '6','2','0','TEST',CONVERT(DATE,'01/05/2016',103),CONVERT(DATE,'10/05/2016',103),
   CONVERT(DATE,'15/05/2016',103),'ABC','S I','V B','1',SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')

This is the create table query:

CREATE TABLE [dbo].[SaleNew](
[SALE_ID] [int] IDENTITY(1,1) NOT NULL,
[ENQ_AID] [bigint] NOT NULL,
[DOCKET_NO] [varchar](50) NOT NULL,
[VEHICLE_MODEL] [varchar](100) NOT NULL,
[SALE_DATE] [date] NULL,
[BOOKING_DATE] [date] NULL,
[DELIVERY_DATE] [date] NULL,
[DEALER_NAME] [date] NULL,
[ENQ_GEN_BY] [varchar](100) NULL,
[EXEC_NAME] [varchar](100) NULL,
[USER_CR] [int] NULL,
[DATE_CR] [date] NULL,
[USER_UP] [int] NULL,
[DATE_UP] [date] NULL,
[SALE_TYPE] [int] NOT NULL,CONSTRAINT [PK_SaleNew] PRIMARY KEY CLUSTERED (
[SALE_ID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON ) ON [PRIMARY]

What could be the reason?

Absolutely no point to anyone speculating without the schema of the destination table. Providing this and the insert query, both with line breaks showing the actual correspondence between columns, would help a lot. — underscore_d
– underscore_d, Commented May 2, 2016 at 10:50
date_cr has type DATE but you store a datetime. And gofr is right! DEALER_NAME must be varchar! — Joe Taras
– Joe Taras, Commented May 2, 2016 at 11:02

Siyavash Hamdi · Accepted Answer · 2016-05-02 12:03:16Z

2

DEALER_NAME type is defined as DATE but you want to INSERT a VARCHAR type value ABC in it.

Execute following query in order to edit the mentioned column type, then Execute your INSERT query.

ALTER TABLE [dbo].[SaleNew]
ALTER COLUMN [DEALER_NAME] VARCHAR(50) NULL

edited May 2, 2016 at 12:03

answered May 2, 2016 at 11:13

Siyavash Hamdi

3,1272 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Conversion failed when converting date and/or time from character string. What could be wrong?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related