Missing Millisecond

[grnzbra]

I have the following code:

UPDATE dbo.tblTable
SET fldDateJoined = '2017-08-14 23:59:59.[highlight yellow]999[/highlight]'
WHERE fldMember = 'John'

SELECT * FROM dbo.tblTable

When I run it I get:
fldMember [tab] fldDateJoined
John [tab][tab][highlight yellow]2017-08-15 00:00:00.000[/highlight]
Joe [tab] [tab] 2017-08-14 11:24:10.480

When I run

UPDATE dbo.tblTable
SET fldDateJoined = '2017-08-14 23:59:59.[highlight yellow]998[/highlight]'
WHERE fldMember = 'John'

SELECT * FROM dbo.tblTable

I get
fldMember fldDateJoined
John [tab][tab]2017-08-14 23:59:59.[highlight yellow]997[/highlight]
Joe [tab] [tab] [highlight #FCE94F][highlight #FCE94F][/highlight][/highlight]2017-08-14 11:24:10.480

Why might this be happening and is there a way to prevent it? The same thing happens if I assign :998 or :999 to a variable and then update the table.

 

[bborissov]

Check this:
Why is SQL Server losing a millisecond?

Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

It's also a reason why you wouldn't store a value that close to midnight by intention of it to belong to that day. Either you store GETDATE() and if you're "unlucky" the value belongs to the next day, although it was stored a millisecond before midnight or you intentionally only store 23:59:00.000, which also remains at that day with the smalldatetime tpe (which has a minute precision only). There almost never will be a reason you want to get as close as possible to the end of a day/month/year.

It's also a reason some DBAs or DB developers don't use BETWEEN with datetimes, as you can't exclude the upper and lower bound values, and instead of [tt]f BETWEEN A AND B[/tt] translate and modify that to the two conditions [tt]A<=f AND f<B[/tt] (whereas BETWEEN would check [tt]A<=f AND f<=B[/tt]), so B can be set up as the midnight of a day you want to exclude, also see thread183-1779551.

There I recommended to use a date type field, when you want and need date precision only. There I also talked about the logic of datetimes. Let me add I recommend or evangelize to use GETDATE() as a default value and take its value for granted as the point in time, you might have rounding there so data belongs to a "wrong day", but what is right day anyway? What would be the date data belongs to? Something stored at midnight exactly surely came from last day and was perhaps entered within the 10 minutes before by an online user, who does so at day time in his time zone. But in the end it doesn't matter, it belongs to one day.

Bye, Olaf.