[經驗分享] 你抓對了查詢的日期區間了嗎?

2017-11-10

8387
0
SQL Server

Getting bit by datetime rounding problem

分享一個最近幫同事處理資料移轉遇到的一個小問題

A君要在一個沒有 primary key 的 table 做資料移轉

phase 1 被告知先移轉一段日期的資料

之後 phase 2 才會有 down time 去轉最後一部份的資料

於是在模擬環境寫了兩段語法如下

INSERT INTO [TestDB].[dbo].[NewTable]
SELECT * FROM [TestDB].[dbo].[OldTable] WITH(NOLOCK) 
WHERE StartTime BETWEEN '2017-10-30 00:00:00.000' AND '2017-10-31 23:59:59.999'

INSERT INTO [TestDB].[dbo].[NewTable]
SELECT * FROM [TestDB].[dbo].[OldTable] WITH(NOLOCK) 
WHERE StartTime >= '2017-11-01 00:00:00.000'

最後在驗證新舊資料筆數的時候發現怎麼兩邊的筆數不一致 ?

原來有個小細節被忽略了所以在查詢日期區間時的進位問題導致重複寫入了相同資料

此狀況在於 SQL Server 對 datetime 的進位處理我們實際來驗證一下好了

USE TestDB
GO

CREATE TABLE OldTable
(
	ID int,
	StartTime datetime
)
GO

INSERT INTO	TestDB.dbo.OldTable (ID, StartTime)
VALUES 
	(1, '2017-10-30 00:00:00.000'),
	(2, '2017-10-30 00:00:00.001'),
	(3, '2017-10-30 23:59:59.996'),
	(4, '2017-10-30 23:59:59.997'),
	(5, '2017-10-30 23:59:59.998'),
	(6, '2017-10-30 23:59:59.999'),
	(7, '2017-10-31 00:00:00.000'),
	(8, '2017-10-31 00:00:00.001'),
	(9, '2017-10-31 23:59:59.996'),
	(10, '2017-10-31 23:59:59.997'),
	(11, '2017-10-31 23:59:59.998'),
	(12, '2017-10-31 23:59:59.999'),
	(13, '2017-11-01 00:00:00.000'),
	(14, '2017-11-01 00:00:00.001'),
	(15, '2017-11-01 23:59:59.996'),
	(16, '2017-11-01 23:59:59.997'),
	(17, '2017-11-01 23:59:59.998'),
	(18, '2017-11-01 23:59:59.999')
GO