pekira pekira - 1 year ago 141
SQL Question

sql conversion of a varchar data type to a datetime data type out-of-range

enter image description here

I am trying to add to a table a group of values on of them is a date.
When trying to add a date i receive the following error:


The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.


i have tried to run the following query's:

INSERT INTO BoxEntries (Date,Value,Description,Empid,EmpName) Values(CAST('27/07/2017 10:24:13' AS DATETIME),'0','Alpha Day','0','Alpha')

INSERT INTO BoxEntries (Date,Value,Description,Empid,EmpName) Values(CONVERT(VARCHAR,'27/07/2017 10:24:13',13),'0','Alpha Day','0','Alpha')

INSERT INTO BoxEntries (Date,Value,Description,Empid,EmpName) Values(CONVERT(VARCHAR,'27-07-2017 10:24:13.000',113),'0','Alpha Day','0','Alpha')

INSERT INTO BoxEntries (Date,Value,Description,Empid,EmpName) Values('27-07-2017 10:24:13.000','0','Alpha Day','0','Alpha')


I have confirmed and 13 or 113 is the time of datatime i want in SQL.
The wired part is that when i try to directly add to the database the values it doesn't give me any errors.

The table:

CREATE TABLE [dbo].[BoxEntries] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Date] DATETIME NOT NULL,
[Value] MONEY NOT NULL,
[Description] VARCHAR (MAX) NOT NULL,
[EmpId] INT NOT NULL,
[EmpName] VARCHAR (MAX) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC) );

Answer Source

mssql format of datetime is 'YYYY-MM-DD HH:MM:SS.mmm'

https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql

so correct query for your case might be:

INSERT INTO BoxEntries ([Date],Value,Description,Empid,EmpName) Values('2017-07-27 10:24:13.000','0','Alpha Day','0','Alpha');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download