Conversion failed when converting date and or time from character string while inserting datetime

Question

I tried creating a table as follows:

create table table1(date1 datetime,date2 datetime);

I tried inserting values as below:

insert into table1 values('21-02-2012 6:10:00 PM','01-01-2001 12:00:00 AM');

I encountered this error:

Cannot convert varchar to datetime.

I tried this as a solution :

insert into table1 values(convert(datetime,'21-02-2012 6:10:00 PM',5)

                          ,convert(datetime,'01-01-2001 12:00:00 AM',5));

But still getting the error saying:

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

Can someone help me with this?

Answer 1

The SQL server supports many formats. Most of the formats are dependent on what settings you have. To solve your issue you can use the ISO-8601 date format that is supported by SQL Server. 

The ISO-8601 format is supported by SQL Server comes in two flavors:

1. YYYYMMDD for just dates.
2. YYYY-MM-DDTHH:MM:SS for dates and times.

In your specific case - use these strings:

To know more about SQL, It's recommended to join Microsoft SQL Training today.

insert into table1 values('2012-02-21T18:10:00', '2012-01-01T00:00:00');

I hope this helps you.