Skip to content

How can i mention AM/PM while inserting time value into a table in sql server?

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
('starbuzz coffee','7:43 am','4/23','cinnamon glazed',6,' too much price')



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

2 People found this is helpful