0

I'm new to sql server since this is the requirement of my client. I can't insert because of the error I'm getting regarding the smalldatatime. What's wrong with my query? I have tried everything but still it gives me error.

Here are the things I tried:

SET IDENTITY_INSERT table1 ON
INSERT INTO table1
(id, timelog) values
(1, 2018-07-24 06:30:50.000)
SET IDENTITY_INSERT table1 OFF

ERROR I GOT:

Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '06'.

SET IDENTITY_INSERT table1 ON
INSERT INTO table1
(id, timelog) values
(1, '2018-07-24 06:30:50.000')
SET IDENTITY_INSERT table1 OFF

ERROR I GOT:

Msg 206, Level 16, State 2, Line 2 Operand type clash: int is incompatible with date

SET IDENTITY_INSERT table1 ON
INSERT INTO table1
(id, timelog) values
(1, 2018-07-24T06:30:50.000)
SET IDENTITY_INSERT table1 OFF

ERROR I GOT:

Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'T06:'

irish
  • 39
  • 2
  • 8
  • 2
    What's the structure of `table1`? Your second approach is correct but it seems you're trying to insert a date on an int column – Josh Part Jul 24 '18 at 17:43
  • Here: CREATE TABLE table1( id int IDENTITY(1,1) PRIMARY KEY, timelog smalldatetime NOT NULL ); – irish Jul 24 '18 at 17:55
  • What's the correct way to insert smalldatetime or datetime? – irish Jul 24 '18 at 17:55
  • 1
    Syntax error, Inserting values in to tables: INSERT INTO table1 (id, timelog) Values (1, '2018-07-24 00:05:44.610') – CR241 Jul 24 '18 at 19:57

1 Answers1

2

The correct syntax is the 2nd, but you are missing the VALUES keyword:

CREATE TABLE table1( id int IDENTITY(1,1) PRIMARY KEY, timelog smalldatetime NOT NULL ); 

SET IDENTITY_INSERT table1 ON
INSERT INTO table1
(id, timelog) values
(1, '2018-07-24 06:30:50.000')
SET IDENTITY_INSERT table1 OFF

select * from table1

Result:

enter image description here

Andrea
  • 11,801
  • 17
  • 65
  • 72
  • but i'm still getting the same error "Msg 206, Level 16, State 2, Line 2 Operand type clash: int is incompatible with date" – irish Jul 25 '18 at 00:58
  • @irish The syntax is correct, see the fiddle [here](http://rextester.com/JWOE35627) – Andrea Jul 25 '18 at 07:35