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

Question

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?

Answer 1

There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have - therefore, these settings might work some times - and sometimes not.

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

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

• YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

or:

• YYYY-MM-DDTHH:mm:ss for dates and times - note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

This is valid for SQL Server 2000 and newer.

So in your specific case - use these strings:

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

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

Alternatively: if you're on SQL Server 2008 or newer, you could also use the DATETIME2 datatype (instead of plain DATETIME) and your current INSERT would just work without any problems! :-) DATETIME2 is a lot better and a lot less picky on conversions - and it's the recommend date/time data types for SQL Server 2008 or newer anyway.

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  

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

Answer 2

Sometimes, the conversion in SQL Server fails 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).

Answer 3

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.

Answer 4

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.

Answer 5

Just update the date format as like bellow

yyyy-MM-dd hh:MM:ss

It solves the problem for me and it works fine

Answer 6

This works for me:

SELECT CONVERT(date, yourDate ,104)

Answer 7

This error is also displayed when the date doesn't exist (e.g., the date '09-31-2021' doesn't exist, because September has a length of 30 days).

Answer 8

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 9

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

Answer 10

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.

Answer 11

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 12

The datetime format actually that runs on sql server is

yyyy-mm-dd hh:MM:ss

Answer 13

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.

Answer 14

You can try this code

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

Answer 15

For me this worked:

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

Answer 16

For me with EF Core, I needed to use OnModelCreating

For my Job Table, CreatedOn smalldatetime field.

modelBuilder.Entity<Job>().Property(x => x.CreatedOn).HasColumnType("datetime")

Answer 17

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

Answer 18

While writing your SQL insert query, write your date in single quotes and use forward slash in date format (DD/MM/YYYY) like this:

insert into table_name (Emp_Id, Name, **DOB**) values
(01, 'Suresh Kumawat', **'22/03/2015'**);

Answer 19

You can also get this message:

when your column is varchar or char with 'NULL' values and you are trying to convert your column to date.

Answer 20

if you use dotnet6 you can validate like this.

someDate = Util.SystemDate.GetValidatedDateTime(someDate);

and then you can use in SQL.