Conversion error when converting date and / or time from character string when inserting date and time

Question

I tried to create a table as follows:

create table table1(date1 datetime,date2 datetime); 

At first I tried to insert the values ​​as shown below,

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

He made a mistake saying

Unable to convert varchar to datetime

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

 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 I still get the error message:

Conversion error while converting date and / or time from character string

Any suggestions?

Answer 1

There are many formats supported by SQL Server - see the MSDN Internet Books on CAST and CONVERT . Most of these formats are dependent on from what settings you have, so these settings can work several times, and sometimes not.

The way to solve this problem is to use the (slightly adapted) ISO-8601 format supported by SQL Server - this format always works - regardless of your SQL Server and setting the date and time.

The ISO-8601 format is supported by SQL Server in two versions:

• YYYYMMDD only for dates (without time part); Pay attention here: no dash! , it is very important! YYYY-MM-DD NOT dependent on dateformat parameters on your SQL Server and will NOT work in all situations!

or

• YYYY-MM-DDTHH:MM:SS for dates and times - note: this format has a dash (but they can be omitted) and a fixed T as a separator between the date and time of your DATETIME .

This is true for SQL Server 2000 and later.

So, in your specific case - use the following lines:

 insert into table1 values('2012-02-21T18:10:00', '2012-01-01T00:00:00'); 

and you should be fine (note: for this you need to use the international 24-hour format, not the 12-hour AM / PM format).

Alternatively : if you are on SQL Server 2008 or later, you can also use the DATETIME2 data type (instead of just DATETIME ) and your current INSERT will work without problems! :-) DATETIME2 much better and much less picky about conversions - and in any case recommend date and time data types for SQL Server 2008 or later.

 SELECT CAST('02-21-2012 6:10:00 PM' AS DATETIME2), -- works just fine CAST('01-01-2012 12:00:00 AM' AS DATETIME2) -- works just fine 

Do not ask me why this whole topic is so complex and somewhat confusing - it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat parameter on your SQL Server.

Answer 2

The simple answer is 5 - Italian "yy", and 105 - 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 an error.

Similarly

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

will give an error where

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

will work.

Answer 3

Converting to an SQL server is sometimes not due to the use of date or time formats. This is simply because you are trying to save incorrect data that is not acceptable to the system.

Example:

Create Table MyTable (MyDate);

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

SQL Server throws the following error:

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

The reason for this error is simply the absence of such a date (Feb-29) in the year (2015).

Answer 4

Whenever possible , literature on the date / time of a culture should be avoided .

There are some protected formats to indicate the date / time as a literal:

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

ODBC (my favorite, as it is treated as a real type immediately)

• {ts'2016-09-15 17:30:00'} - time stamp
• {d'2016-09-15'} - date only
• {t'17:30:00'} - Only time

ISO8601 (best for everyone)

• '2016-09-15T17:30:00' - know about T in the middle!

Unpronounceable (tiny risk of making a mistake as a number)

• '20160915' - for pure date only

Nice to keep in mind: incorrect dates tend to appear with strange errors

• No June 31 or February 30 ...

Another reason for weird conversion errors: Execution order!

SQL-Server knows well what to do in order of execution, which might not have been expected. Your written expression looks like the conversion is done before , some type-related action takes place, but the engine decides - why ... - make the transition at a later stage.

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

Answer 5

the best way is this code

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

Answer 6

 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, 

Answer 7

Please try this.

SQL Server expects dates in MM / DD / YYYY format if the default language is set to English. Save the datepicker value for sql2008 database. My field type is datetime in the database. dpdob is my date picker 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 request.

Answer 8

Just update the date format as shown below.

 yyyy-MM-dd hh:MM:ss 

It solves the problem for me and it works great

Answer 9

The date and time format that runs on sql server,

 yyyy-mm-dd hh:MM:ss 

Answer 10

set Culture to English from web.config file

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

for example, if you set the culture to Arabic, time will be

09/22/2017 02:16:57 ص

and you get an error message: Conversion error when converting date and / or time from a character string when inserting date and time