CODEWITHSUNDEEP
sqlsql-serverdatetimejdbc
Dave
Dave

Reputation: 997

Can changing from datetime to datetime2 ever cause bugs?

My team would like to update our SQL Server datetime columns (which only have 3 digits of precision with weird rounding rules) to use datetime2 with 7 digit precision.

Are there any well known edge cases where this kind of change would cause a problem or require modifying business logic that uses those dates?

We use Java so any low level interpretation would be handled by the driver jar. I just can't think of a situation when having more precision would be a bad thing (storage space is not an issue), but these changes make some people nervous, and if I had a dollar for every time I had to fix a bug I couldn't think of ahead of time I'd be rich.

[edited to add: we would also update all stored procedures to use datetime2 instead of datetime for all variables and temp tables, so you wouldn't have e.g. a temp table created with datetime that would store datetime2 values].

Upvotes: 1

Views: 417

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272006

Speaking of increasing precision, if you have code such as the following which is designed specifically for datetime:

WHERE dtcol BETWEEN '2019-01-01' AND '2019-01-31 23:59:59.997' -- all Jan 2019 datetime values

Then it must be changed to:

WHERE dtcol BETWEEN '2019-01-01' AND '2019-01-31 23:59:59.9999999'
/*
 * inside the literal, the number of nines after decimal must be
 * equal to/greater than the fractional precision of the column
 * e.g. the datetime2(3) value '2019-01-31 23:59:59.999'
 * <= '2019-01-31 23:59:59.9999' is true
 * <= '2019-01-31 23:59:59.999' is true
 * <= '2019-01-31 23:59:59.99' is false
 *
 */

Or better:

WHERE dtcol >= '2019-01-01'
  AND dtcol <  '2019-02-01' -- now the comparison is independent of datatype and precision

As for datatype change, be aware of the following constructs (tricks) hidden inside your code:

SELECT dtcol + 1

While this adds 1 day to a datetime value, this code simply fails on datetime2 datatype. Use DATEADD function instead.

SELECT DATEADD(..., DATEDIFF(..., 0, dtcol), 0)

This is often used to zero-out month/day/hour/minute/etc part of a datetime like so:

SELECT dtcol                                        -- 2019-08-27 16:17:18.123
     , DATEADD(MONTH, DATEDIFF(MONTH, 0, dtcol), 0) -- 2019-08-01 00:00:00.000
     , DATEADD(DAY,   DATEDIFF(DAY,   0, dtcol), 0) -- 2019-08-27 00:00:00.000
     , DATEADD(HOUR,  DATEDIFF(HOUR,  0, dtcol), 0) -- 2019-08-27 16:00:00.000

The 0s in the above example are assumed to be a datetime value of 1753-01-01 resulting in conversions from datetime2 to datetime and the return type is datetime.

Use DATETIME2FROMPARTS instead for such purpose or rewrite the code as follows:

SELECT dtcol                                                                                    -- 2019-08-27 16:17:18.12345
     , DATEADD(MONTH, DATEDIFF(MONTH, '0001-01-01', dtcol), CAST('0001-01-01' AS DATETIME2(5))) -- 2019-08-01 00:00:00.00000
     , DATEADD(DAY,   DATEDIFF(DAY,   '0001-01-01', dtcol), CAST('0001-01-01' AS DATETIME2(5))) -- 2019-08-27 00:00:00.00000
     , DATEADD(HOUR,  DATEDIFF(HOUR,  '0001-01-01', dtcol), CAST('0001-01-01' AS DATETIME2(5))) -- 2019-08-27 16:00:00.00000

Upvotes: 1

Dave
Dave

Reputation: 997

Here's something I found:

DECLARE @dt DATETIME = SYSDATETIME()
DECLARE @dt2 DATETIME2(7) = SYSDATETIME()
DECLARE @str char(27) = @dt
DECLARE @str2 char(27) = @dt2
SELECT @str, ISDATE(@str), @str2, ISDATE(@str2)

Prints Aug 27 2019 10:22AM, 1, 2019-08-27 10:22:25.9935078, 0

Two things going on:

  1. datetime and datetime2 convert to char in different formats.
  2. ISDATE() returns false when there are more than 3 digits of precision.

Upvotes: 2

HoneyBadger
HoneyBadger

Reputation: 15130

One thing that pops into my head is this:

DECLARE @test DATETIME = '20100101'

SELECT  @test
,       @test - 1

Try and replace it with DATETIME2

Upvotes: 6

Related Questions