Conversion failed when converting date and/or time from character string SQL

Question

CREATE TABLE TICKET
(
    TicketID int primary key,
    StartLoc varchar(20),
    FinalLoc varchar(20),
    price int,
    DateOfPurchase date,
    SeatNumber int,
    TrainID int,
    DepartureDate date ,
);

insert into TICKET
values (1,'HaydarPasa','Sirkeci',20,'20,18-06-12 10:34:09 AM',10,1,'20-06-12 10:34:09 AM')

I'm using SQL Server Management 2008 R2 version.When execute the query it says

Conversion failed when converting date and/or time from character string SQL

You are missing 2 quotes here '20,18-06-12 10:34:09 AM' (after 20 and before 18) — juergen d
May be date format is wrong. BTW should not those date field be date time instead of date since you are inserting time too?? — qamar

HoneyBadger HoneyBadger · Accepted Answer · 2015-01-02T10:42:26

@juergenD is right, the string 20,18-06-12 10:34:09 AM is not a date string. Is the 20 part of the insert? if so your table definition is off. I expect a copy-paste error here...

Additionally: When i try your query on my machine, it still gives an error converting to date. If I change the dates to the format yyyy-MM-dd hh:mm:ss AM it does work. Might be to do with local settings though.

Conversion failed when converting date and/or time from character string SQL

1 Answers