CODEWITHSUNDEEP
sql-server
Natalia Kr
Natalia Kr

Reputation: 1

Conversion failed when converting date and/or time from character string in MS SQL Server 2017

I am a newbie to MS SQL Server, I get the error

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

running the following query:

CREATE TABLE DateVals 
(
    dt datetime,
    t time,
    dt2 datetime2,
    dts datetimeoffset
);
GO

INSERT INTO DateVals (dt, t, dt2, dts)
VALUES ('2011-01-01 23:10:10.003', '23:10:10.003', '2011-01-01 23:10:10.003', '2011-01-01 23:10:10.003 +02.00');
GO

Could you tell me why I get this error?

PS. My OS language is Polish

Upvotes: 0

Views: 169

Answers (2)

marc_s
marc_s

Reputation: 754518

Converting a string literal into a DATETIME column is quite an adventure - and DATETIME is very peculiar about what formats are supported.

The best 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:

  • YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

or:

  • YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

So in your case, try either:

INSERT INTO DateVals (dt, t, dt2, dts)
VALUES ('2011-01-01T23:10:10', '23:10:10.003', '2011-01-01 23:10:10.003', '2011-01-01 23:10:10.003 +02:00');

(you also need to use a : in the +02:00 moniker for the DATETIMEOFFSET - not a dot or comma)

or make dt in your table a column of type DATETIME2(n) (instead of DATETIME).

This is valid for SQL Server 2000 and newer.

If you use SQL Server 2008 or newer and the DATE datatype (only DATE - not DATETIME!), then you can indeed also use the YYYY-MM-DD format and that will work, too, with any settings in your SQL Server.

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.

The recommendation for SQL Server 2008 and newer is to use DATE if you only need the date portion, and DATETIME2(n) when you need both date and time. You should try to start phasing out the DATETIME datatype if ever possible

Upvotes: 1

dfundako
dfundako

Reputation: 8314

Your query is correct, just need a colon instead of a comma in the datetimeoffset when you use +02:00.

INSERT INTO DateVals (dt, t, dt2, dts)
VALUES ('2011-01-01 23:10:10.003', '23:10:10.003', '2011-01-01 23:10:10.003', '2011-01-01 23:10:10.003 +02:00');

Instead of 

INSERT INTO DateVals (dt, t, dt2, dts)
VALUES ('2011-01-01 23:10:10.003', '23:10:10.003', '2011-01-01 23:10:10.003', '2011-01-01 23:10:10.003 +02.00');

Upvotes: 1

Related Questions