I was trying to create a table as follows,
create table table1(date1 datetime,date2 datetime);
First I tried inserting values as below,
insert into table1 values('21-02-2012 6:10:00 PM','01-01-2001 12:00:00 AM');
It has given error saying,
Cannot convert varchar to datetime
Then I tried below format as one of the post suggested by our stackoverflow,
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 am still getting the error saying,
Conversion failed when converting date and/or time from character string
There are many formats supported by SQL Server – see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have – therefore, these settings might work some times – and sometimes not.
The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server – this format works always – regardless of your SQL Server language and dateformat settings.
The ISO-8601 format is supported by SQL Server comes in two flavors:
YYYYMMDDfor just dates (no time portion); note here: no dashes!, that’s very important!
YYYY-MM-DDis NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!
YYYY-MM-DDTHH:MM:SSfor dates and times – note here: this format has dashes (but they can be omitted), and a fixed
Tas delimiter between the date and time portion of your
This is valid for SQL Server 2000 and newer.
So in your specific case – use these strings:
insert into table1 values('2012-02-21T18:10:00', '2012-01-01T00:00:00');
and you should be fine (note: you need to use the international 24-hour format rather than 12-hour AM/PM format for this).
Alternatively: if you’re on SQL Server 2008 or newer, you could also use the
DATETIME2 datatype (instead of plain
DATETIME) and your current
INSERT would just work without any problems! 🙂
DATETIME2 is a lot better and a lot less picky on conversions – and it’s the recommend date/time data types for SQL Server 2008 or newer anyway.
SELECT CAST('02-21-2012 6:10:00 PM' AS DATETIME2), -- works just fine CAST('01-01-2012 12:00:00 AM' AS DATETIME2) -- works just fine
Don’t ask me why this whole topic is so tricky and somewhat confusing – that’s just the way it is. But with the
YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.