Geek Answers Handbook

Differences between SQL DATEDIFF constraints between databases on the same SQL instance?

I have a secret to forcing data. I see two different behaviors for the same query, and I cannot understand why.

This is an excerpt from the corresponding part of the request in question with fixed values. The first value represents “today” in our query and is configured with the same data type with an explicit CAST:

-- edited to change dates to ISO 8601 literal format to avoid ambiguity
SELECT DATEDIFF(dd,CAST('2014-03-24' AS SmallDateTime),'0001-01-01')

ISO 8601 Date in literal format: https://msdn.microsoft.com/en-us/library/ms187819.aspx

We have two different databases on the same instance of the SQL server.

One of them returns zero rows, as you would expect.

The server returns an error about the date range '1/1/0001':

Msg 242, Level 16, State 3, Line 1
Converting the varchar data type to the datetime data type resulted in a value out of range.

Two servers return a value that looks correct:

-735315

The problematic date is almost certainly "1/1/0001", and it fails because I expect the default datetime to be lower than the minimum SQL data time of January 1, 1753 ( https://msdn.microsoft.com /en-us/library/ms187819.aspx ).

According to the MSDN page for dated ( https://msdn.microsoft.com/en-US/library/ms189794(v=SQL.105).aspx ), it can take the following values:

 startdate is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset

The translation results for each of them are identical between the servers and are listed here:

SELECT CAST('0001-01-01' As time) -- works: 00:00:00.0000000
SELECT CAST('0001-01-01' As date) -- works: 0001-01-01
SELECT CAST('0001-01-01' As smalldatetime) -- error: The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.
SELECT CAST('0001-01-01' As DateTime) -- error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
SELECT CAST('0001-01-01' As datetime2) -- works: 0001-01-01 00:00:00.0000000
SELECT CAST('0001-01-01' As datetimeoffset) -- works: 0001-01-01 00:00:00.0000000 +00:00

DateTime, .

, , , .

SQL, .

, , , , ( SQL Server Management Studio):

( , ):

(database) > Right Click > Properties > General > Maintenance > Collation
Database one: SQL_Latin1_General_CP1_CI_AS
Database two: SQL_Latin1_General_CP1_CI_AS

:

(database) > Right Click > Properties > Options > Misc. > Date Correlation Optimization Enabled
Database one: False
Database two: False

:

(database) > Right Click > Properties > Options > Containment > Two Digit Year Cutoff
Database one: 2049
Database two: 2049


DBCC USEROPTIONS
Database one, dateformat: mdy
Database two, dateformat: mdy
(other settings appear identical)

, , .

- ? , ?

:

  • ISO, - . .
  • ​​ DBCC USEROPTOINS dateformat, mdy
+4
1

https://dba.stackexchange.com/questions/96101/sql-datediff-coercion-differences-between-databases-on-same-sql-instance

DBA, .

SELECT compatibility_level
FROM sys.databases WHERE name = 'FirstDatabase'

90

VS

SELECT compatibility_level
FROM sys.databases WHERE name = 'SecondDatabase'

110

, : https://dba.stackexchange.com/questions/44908/what-is-the-actual-behavior-of-compatibility-level-80

. " /"

, , , DateDiff DateTime2 " " . 90 , , DateTime , , .

Tab Alleman .

0

More articles:


All Articles