CODEWITHSUNDEEP
sqlsql-server
Nischey D
Nischey D

Reputation: 199

SQL:Conversion failed error

I am using Microsoft SQL Server 2016 (SP1-CU3) (KB4019916). And I have a table with columns of data type datetime and date.

I am trying to insert the datetime value using this query, but I keep getting error

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

My code:

INSERT INTO [mytable] ([Date1], [Date2], [Date3])
VALUES (CONVERT(DATETIME, '2017-09-22 20:31:48.000', 126),
        CONVERT(DATE, '2017-09-22', 126),
        CONVERT(DATETIME, '2017-09-23 04:07:46.000')

I have tried both cast as well as convert and still getting same error.

INSERT INTO [mytable]([Date1], [Date2], [Date3])
VALUES 
     (CAST('2017-09-22 20:31:48.000' AS DATETIME),
      CAST('2017-09-22' AS DATE),
      CAST('2017-09-23 04:07:46.000' AS DATETIME))

Can someone help me on this?

Upvotes: 0

Views: 80

Answers (2)

GuidoG
GuidoG

Reputation: 12014

Use a language neutral format for this, so you are sure it will work on any sql server. see here for more info http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes

an example of language neutral format is this 

select CONVERT(datetime, '20170922 20:31:48')

This will work an any sql server database, regardless of its language settings.

Also the convert is not needed for your intention.
you can just write it like this :

INSERT INTO [mytable]
   ( [Date1], [Date2], [Date3] )
Values 
   ( '20170922 20:31:48', '20170922', '20170923 04:07:46' )

This does assumes that Date1, Date2 and Date3 are of type DateTime or DateTime2 (Date2 can also be of type Date)

Upvotes: 1

Mike R
Mike R

Reputation: 691

What is your table schema like? I'm assuming something along these lines? 

CREATE TABLE dbo.myTable
(
    Date1 DATETIME
   ,Date2 DATE
   ,Date3 DATETIME
)

Your first example (using CONVERT) was failing with the DateTimes because the style you're using is 126 which expects no spaces in the DateTime (e.g. 2017-09-22T20:31:48.000). On the above schema your 2nd insert (using CAST) succeeds. 

INSERT INTO dbo.myTable
(
   Date1,Date2,Date3
)
VALUES 
(CAST('2017-09-22 20:31:48.000' AS datetime ),CAST('2017-09-22' AS datetime),CAST('2017-09-23 04:07:46.000' AS datetime ))

To correct your first example (using CONVERT) you can just take out the 126 style and it'll run smoothly.

CONVERT(DATETIME, '2017-09-22 20:31:48.000')

Upvotes: 1

Related Questions