SQL INSERT INTO syntax

Question

Sorry if this is a little low level but I am a student learning SQL on SQL Server management Studio and trying to add some dummy data to a database I am using the following

INSERT INTO dbo.Bookings (bookingid ,bookingdate ,customerid ,airportid ,outboundflight ,dateout ,timeout ,location ,inboundflight ,datein ,timein)

VALUES (1, 20160225, 2, 'STN', 'JJ2305', 20160316, 0950 , null, 'JJ2306', 20160416, 1800 )

But I am getting this error message :

Message: Operand type clash: int is incompatible with date

So I checked the db and this is a printout of the structure

       (<bookingid, nchar(10),>
       ,<bookingdate, date,>
       ,<customerid, int,>
       ,<airportid, nvarchar(5),>
       ,<outboundflight, nchar(10),>
       ,<dateout, date,>
       ,<timeout, time(7),>
       ,<location, nchar(10),>
       ,<inboundflight, nchar(10),>
       ,<datein, date,>
       ,<timein, time(7)>

)

As you can see non of the columns for dates that I am attempting to add a date to are int , in fact there is only one int and that should hold the '2'

Can anyone put me out of my misery as I have tried to understand/fix this for two days (off and on) without and assignment due date is looming !

Thanks

David, checked that one out and this appears to be different as its about a clash of input type between date and int, although I can't see where that clash is as the only column that is an int is the third one and that should have 2 entered into it as I said in my question. — Andrew Bowles, Mar 23 '16 at 14:00

score 3 · Accepted Answer · answered Mar 23 '16 at 13:35

3

Missing the quotes.

Try

VALUES (1, '20160225', 2, 'STN', 'JJ2305', '20160316', 0950 , null, 'JJ2306', '20160416', 1800 )

answered Mar 23 '16 at 13:35

vbilopav

156
9

copied you line into the query and this is what I am getting ! Msg 206, Level 16, State 2, Line 4 Operand type clash: int is incompatible with time – Andrew Bowles Mar 23 '16 at 13:57
Actually was a little slow on the uptake but your answer fixed the first error, as in date, but then threw up the next error which was time so I followed your missing quotes answer added them around the date and then it worked (although the time format appears to be incorrect too) but I now have a line in that table - Thanks so much. – Andrew Bowles Mar 23 '16 at 14:09

score 0 · Answer 2 · answered Mar 23 '16 at 13:48

You have to provide quotes '' for datatype date in following:

INSERT INTO dbo.Bookings (bookingid ,bookingdate ,customerid ,airportid ,outboundflight ,dateout ,timeout ,location ,inboundflight ,datein ,timein)

VALUES (1, 20160225, 2, 'STN', 'JJ2305', 20160316, 0950 , null, 'JJ2306', '20160416', 1800 )

SQL INSERT INTO syntax

2 Answers2