You receive this error when you try to convert a string that is a not compatible with the datetime data type to a datetime.
It usually occurs when you want to insert a string that is not compatible with the datetime data type into a column of datetime data type.
For example, with the help of the following script, let’s create a table named datetimeconvert in the TestDB database and try to insert a value as follows.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
USE [TestDB] GO CREATE TABLE [dbo].[datetimeconvert]( [database_name] [varchar](50) NULL, [login_name] [varchar](50) NULL, [created_date] [datetime] NULL ) ON [PRIMARY] INSERT INTO [dbo].[datetimeconvert] ([database_name] ,[login_name] ,[created_date]) VALUES ('myDBName','2019-01-22','MyLogin') |
As you can see, we received the error we expected. We received this error because we changed the order of the columns created_date and login_name when inserting. When we change the script as follows, it will be completed successfully without error.
|
1 2 3 4 5 6 |
INSERT INTO [dbo].[datetimeconvert] ([database_name] ,[login_name] ,[created_date]) VALUES ('myDBName','MyLogin','2019-01-22') |
