I am trying to insert values in time and column of table(doughnut_ratings) separately, but it is giving following syntax error:
Msg 241, Level 16, State 1, Line 65 Conversion failed when converting date and/or time from character string.
I am writing the following query, please suggest
create table doughnut_ratings
(
location varchar(30),
time time,
date date,
type char(30),
ratings int,
comments varchar(max),
)
insert into doughnut_ratings
values
('starbuzz coffee','7:43 am','4/23','cinnamon glazed',6,' too much price')
Advertisement
Answer
The problem isn’t the time ('7:43 am'), which can be converted without issue. TRY_CONVERT(time,'7:43 am') returns 07:43:00.0000000. DB<>Fiddle.
The problem is your “date”: '4/23'. That isn’t a date. A date is made up of the year, month and day, and it needs all of those. You need to use a format like yyyyMMdd or yyyy-MM-dd. If that date is this year, for example, it would be '2020-04-23'.
You need to fix your data, then you can INSERT it. 4/23 could represent many things. April 1923/April 2023; if so what day?. 23rd April; if so what year? 4th day of the 23rd year or 23rd day of the 4th year (no idea what that would be)?