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).

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,

the best way is this code

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

The datetime format actually that runs on sql server is

yyyy-mm-dd hh:MM:ss

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

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

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"](

[To see links please register here]

) and here is [the related question](

[To see links please register here]

).