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
– juergen d, Commented Jan 2, 2015 at 10:38
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
– qamar, Commented Jan 2, 2015 at 10:38

HoneyBadger · Accepted Answer · 2015-01-02 10:48:39Z

1

@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.

edited Jan 2, 2015 at 10:48

answered Jan 2, 2015 at 10:42

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user4412122 Over a year ago

Thanks this solved my problem..insert into TICKET values (1,'HaydarPasa','Sirkeci',20,'2012-06-20',10,1,'2012-06-20 10:34:09')

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related