[sql] Conversion failed when converting date and/or time from character string while inserting datetime

I was trying to create a table as follows,

create table table1(date1 datetime,date2 datetime);

First I tried inserting values as below,

insert into table1 values('21-02-2012 6:10:00 PM','01-01-2001 12:00:00 AM');

It has given error saying,

Cannot convert varchar to datetime

Then I tried below format as one of the post suggested by our stackoverflow,

insert into table1 values(convert(datetime,'21-02-2012 6:10:00 PM',5)
                          ,convert(datetime,'01-01-2001 12:00:00 AM',5));

But am still getting the error saying,

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

Any suggestions?

The conversion in SQL server fails sometimes not because of the Date or Time formats used, It is Merely because you are trying to store wrong data that is not acceptable to the system.

Example:

Create Table MyTable (MyDate);

Insert Into MyTable(MyDate) Values ('2015-02-29');

The SQL server will throw the following error:

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

The reason for this error is simply there is no such date (Feb-29) in Year (2015).

The datetime format actually that runs on sql server is

yyyy-mm-dd hh:MM:ss

For me this worked:

INSERT INTO [MyTable]
           ([ValidFrom]
           ,[ValidTo])
       VALUES
           ('2020-01-27 14:54:11.000'
           ,'2023-01-27 14:52:50.000')

convert(datetime2,((SUBSTRING( ISNULL(S2.FechaReal,e.ETA),7,4)+'-'+ SUBSTRING( ISNULL(S2.FechaReal,e.ETA),4,2)+'-'+ SUBSTRING( ISNULL(S2.FechaReal,e.ETA),1,2) + ' 12:00:00.127')))  as fecha,

This is how to easily convert from an ISO string to a SQL-Server datetime:

INSERT INTO time_data (ImportateDateTime) VALUES (CAST(CONVERT(datetimeoffset,'2019-09-13 22:06:26.527000') AS datetime))

Source https://www.sqlservercurry.com/2010/04/convert-character-string-iso-date-to.html

You can try this code

select (Convert(Date, '2018-04-01'))

I'm Tried this and it's working with me :

SELECT CONVERT(date, yourDate ,104)

I had this issue when trying to concatenate getdate() into a string that I was inserting into an nvarchar field.

I did some casting to get around it:

 INSERT INTO [SYSTEM_TABLE] ([SYSTEM_PROP_TAG],[SYSTEM_PROP_VAL]) VALUES 
   (
    'EMAIL_HEADER',
    '<h2>111 Any St.<br />Anywhere, ST 11111</h2><br />' + 
        CAST(CAST(getdate() AS datetime2) AS nvarchar) + 
    '<br /><br /><br />'
   )

That's a sanitized example. The key portion of that is:

...' + CAST(CAST(getdate() AS datetime2) AS nvarchar) + '...

Casted the date as datetime2, then as nvarchar to concatenate it.

Please Try this.

SQL Server expects dates in MM/DD/YYYY format,If English is set as your default language.Here am saving datepicker value to sql2008 database.My field type is datetime in database.dpdob is my datepicker name.

           Dim test = dpdob.Text.Replace("-", "/")
           Dim parts As String() = test.Split(New Char() {"/"c})
           Dim firstPart As String = parts(0)
           Dim thirdPart As String = parts(2)
           Dim secondPart As String = parts(1)
           Dim test1 = secondPart + "/" + firstPart + "/" + thirdPart
           Dim dob = test1

Now use dob in your insert query.

Simple answer - 5 is Italian "yy" and 105 is Italian "yyyy". Therefore:

SELECT convert(datetime,'21-02-12 6:10:00 PM',5)

will work correctly, but

SELECT convert(datetime,'21-02-12 6:10:00 PM',105)

will give error.

Likewise,

SELECT convert(datetime,'21-02-2012 6:10:00 PM',5)

will give error, where as

SELECT convert(datetime,'21-02-2012 6:10:00 PM',105)

will work.

Just update the date format as like bellow

yyyy-MM-dd hh:MM:ss

It solves the problem for me and it works fine

the best way is this code

"select * from [table_1] where date between convert(date,'" + dateTimePicker1.Text + "',105) and convert(date,'" + dateTimePicker2.Text + "',105)"

Whenever possible one should avoid culture specific date/time literals.

There are some secure formats to provide a date/time as literal:

All examples for 2016-09-15 17:30:00

ODBC (my favourite, as it is handled as the real type immediately)

• {ts'2016-09-15 17:30:00'} --Time Stamp
• {d'2016-09-15'} --Date only
• {t'17:30:00'} --Time only

ISO8601 (the best for everywhere)

• '2016-09-15T17:30:00' --be aware of the T in the middle!

Unseperated (tiny risk to get misinterpreted as number)

• '20160915' --only for pure date

Good to keep in mind: Invalid dates tend to show up with strange errors

• There is no 31st of June or 30th of February...

One more reason for strange conversion errors: Order of execution!

SQL-Server is well know to do things in an order of execution one might not have expected. Your written statement looks like the conversion is done before some type related action takes place, but the engine decides - why ever - to do the conversion in a later step.

Here is a great article explaining this with examples: Rusano.com: "t-sql-functions-do-no-imply-a-certain-order-of-execution" and here is the related question.

set Culture to english from web.config file

  <globalization uiCulture="en-US" culture="en-US" />

for example if you set the culture to arabic the thime will be

?22?/09?/2017? 02:16:57 ?

and you get the error:Conversion failed when converting date and/or time from character string while inserting datetime