0

According to the MSDN documentation datetime2 has a range from 0001-01-01 to 9999-12-31. It also says that the ODBC string literal looks as follows: 

{ ts 'yyyy-mm-dd hh:mm:ss[.fractional seconds]' }

Why do the last three statements fail? They only succeed when the value is larger than 1753-01-01 and the ODBC syntax is not used.

CREATE TABLE Book (Id INTEGER IDENTITY NOT NULL, ReleasedOn DATETIME2)

INSERT INTO Book VALUES ('0001-01-01 00:00:00')
INSERT INTO Book VALUES ('0501-01-01 00:00:00')
INSERT INTO Book VALUES ('1752-12-31 00:00:00')
INSERT INTO Book VALUES ('1753-01-01 00:00:00')
INSERT INTO Book VALUES ('1902-09-17 00:00:00')


SELECT * FROM Book WHERE ReleasedOn = {ts '1753-01-01 00:00:00'} -- OK
SELECT * FROM Book WHERE ReleasedOn = {ts '1902-09-17 00:00:00'} -- OK

SELECT * FROM Book WHERE ReleasedOn = '0001-01-01 00:00:00' -- OK
SELECT * FROM Book WHERE ReleasedOn =' 0501-01-01 00:00:00' -- OK
SELECT * FROM Book WHERE ReleasedOn = '1752-12-31 00:00:00' -- OK

SELECT * FROM Book WHERE ReleasedOn = {ts '0001-01-01 00:00:00'} -- Error
SELECT * FROM Book WHERE ReleasedOn = {ts '0501-01-01 00:00:00'} -- Error
SELECT * FROM Book WHERE ReleasedOn = {ts '1752-12-31 00:00:00'} -- Error

-- Error:
-- Server: Msg 241, Level 16, State 1, Line 1
-- Conversion failed when converting date and/or time
-- from character string.
Antineutrino
  • 1,093
  • 3
  • 10
  • 26
  • most likely you need to update ODBC drivers – cha Apr 30 '14 at 06:56
  • I use SQL Server Native Client 11.0 in version 2011.110.3000.00. So there's a newer version which can handle smaller datetimes? – Antineutrino Apr 30 '14 at 07:00
  • When I check this in a fiddle, I find that the supplied values seem to cause some of the problem (ignoring the typo on the 4th select). See http://sqlfiddle.com/#!3/a1e1c/7 – Turophile Apr 30 '14 at 07:21
  • @Turophile the fixed fiddle statements are still throwing same error http://rextester.com/UNEJ79471 – Deepshikha Apr 30 '14 at 08:30

1 Answers1

2

According to MSDN:

SQL Server always treats ODBC data as being of the datetime data type.
*Conversion Notes*
1.ODBC string literals are mapped to the datetime data type. Any assignment 
  operation from ODBC DATETIME literals into date, time, datetime2, or datetimeoffset
  types will cause an implicit conversion between datetime and these types as 
  defined by the conversion rules.

and Date range for Datetime datatype is : 

January 1, 1753, through December 31, 9999

so that's the reason why SQL server throws an error when ODBC string literal is used in last 3 statements, because there it does an implicit conversion to datatype Datetime and not Datetime2 and values supplied are out of range values possible for Datetime datatype hence the error.

I hope this helps!!!

Deepshikha
  • 9,896
  • 2
  • 21
  • 21