I would love it if this were found to be a bug in my code. That would be easily fixable. The code I am using to plug this data in is as follows (names changed to protect the innocent), where @pTableID is a parameter declared as bigint, and @pTypeList is a second parameter defined as a user-defined table type with one column called StringEntry of type varchar(max). The first bit of code works, but the second doesn't always work. The EffectiveDate column in what I'm calling MyTable1 and the EffectiveDate column in MyTable2 are both always correct. The ModifyDate column in MyTable1 is not always correct. This makes no sense to me. By the way, you can see where I have commented out six different selects to show the value of @tEffectiveDate three times after it is set. They are always the same. I was just wondering if possibly the value might be set at usage time rather than at setting time, but that seems not the case:=============== Code snippet 1 ========================DECLARE @tTypes TABLE (TableID bigint, Type int, EffectiveDate datetime2);DECLARE @tEffectiveDate datetime2SET @tEffectiveDate = GetDate()-- select 1, @tEffectiveDateINSERT [MyTable1] ([TableID], [Data1], [Data2], [Data3], [Data4], [EffectiveDate])VALUES (@pTableID, @pData1, @pData2, @pData3, @pData4, @tEffectiveDate); -- select 2, @tEffectiveDateINSERT @tTypes ([Type])(SELECT StringEntry AS Type FROM @pTypeList)UPDATE @tTypesSET [TableID] = @pTableID, [EffectiveDate] = @tEffectiveDate INSERT [MyTable2] ([Table1ID], [Type], [EffectiveDate]) (SELECT [TableID], [Type], [EffectiveDate] FROM @tTypes) -- select 3, @tEffectiveDate=============== End Code snippet 1 ====================
=============== Code snippet 2 ========================DECLARE @tTypes TABLE (TableID bigint, Type int, EffectiveDate datetime2);DECLARE @tEffectiveDate datetime2SET @tEffectiveDate = GetDate()-- select 4, @tEffectiveDateUPDATE [MyTable1]SET [Data1] = @pData1, [Data2] = @pData2, [Data3] = @pData3, [Data4] = @pData4, [ModifyDate] = @tEffectiveDateWHERE [TableID] = @pTableID-- select 5, @tEffectiveDateINSERT @tTypes ([Type])(SELECT StringEntry AS Type FROM @pTypeList)UPDATE @tTypesSET [TableID] = @pTableID, [EffectiveDate] = @tEffectiveDate INSERT [MyTable2] ([Table1ID], [Type], [EffectiveDate]) (SELECT [TableID] as Table1ID, [Type], [EffectiveDate] FROM @tTypes)-- select 6, @tEffectiveDate=============== End Code snippet 2 ====================
=============== Table definitions ====================CREATE TABLE [dbo].[MyTable1]( [TableID] [bigint] NOT NULL, [Data1] [varchar](128) NULL, [Data2] [int] NOT NULL, [Data3] [bigint] NULL, [Data4] [bigint] NULL, [EffectiveDate] [datetime2(7)] NOT NULL, [Modifydate] [datetime2(7)] NULL, CONSTRAINT [PK__target__72F2C0F0214BF109] PRIMARY KEY CLUSTERED ( [TableID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
CREATE TABLE [dbo].[MyTable2]( [Table2ID] [int] IDENTITY(1,1) NOT NULL, [Table1ID] [bigint] NOT NULL, [Type] [int] NOT NULL, [EffectiveDate] [datetime2(7)] NOT NULL, CONSTRAINT [PK__device__2FAC98AB3CF40B7E] PRIMARY KEY CLUSTERED ( [DeviceID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])ALTER TABLE [dbo].[MyTable2] WITH CHECK ADD CONSTRAINT [MyTable1_MyTable2_FK1] FOREIGN KEY ([Table1ID])REFERENCES [dbo].[MyTable1] ([TableID])
=============== End Table definitions ====================After seeing the comments on my posting, I changed this so that I used SYSDATETIME instead of GETDATE as suggested. That really shows the problem more clearly. I ran my tests, and what I saw every time was that the ModifyDate column in MyTable1 was always being rounded to three decimal places of precision, whereas the EffectiveDate columns in both tables were giving me data to all seven places. The following is the output of one run:TableID Data1 Data2 Data3 Data4 EffectiveDate Modifydate1 2 2 2 2 2012-04-10 09:37:58.0403046 2012-04-10 09:37:58.0570000Table2ID Type Table1ID EffectiveDate205 1 1 2012-04-10 09:37:58.0403046206 2 1 2012-04-10 09:37:58.0403046207 3 1 2012-04-10 09:37:58.0403046208 4 1 2012-04-10 09:37:58.0403046209 5 1 2012-04-10 09:37:58.0403046210 6 1 2012-04-10 09:37:58.0403046211 1 1 2012-04-10 09:37:58.0563062212 2 1 2012-04-10 09:37:58.0563062213 3 1 2012-04-10 09:37:58.0563062214 4 1 2012-04-10 09:37:58.0563062215 5 1 2012-04-10 09:37:58.0563062216 6 1 2012-04-10 09:37:58.0563062
My question now is, why would two fields with the same definition be acting differently, where one is rounding and the other isn't. The only difference I can see is that both actions on EffectiveDate columns are inserts, whereas the one action on ModifyDate is an update. This really shouldn't be the reason why these are acting differently.Any further ideas on this would be highly appreciated. I'm really quite at a loss with this one. 
Milliseconds off on only some insertions
Posted - 2012-04-09 : 18:35:28
