CODEWITHSUNDEEP
sqlsql-server
pekira
pekira

Reputation: 102

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

Upvotes: 1

Views: 1177

Answers (1)

Slavik
Slavik

Reputation: 6827

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

https://learn.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');

Upvotes: 1

Related Questions