INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Missing Millisecond

Missing Millisecond

(OP)
I have the following code:

CODE --> T-SQL

UPDATE dbo.tblTable
SET fldDateJoined = '2017-08-14 23:59:59.999'
WHERE fldMember = 'John'

SELECT * FROM dbo.tblTable 

When I run it I get:
fldMember      fldDateJoined
John         2017-08-15 00:00:00.000
Joe           2017-08-14 11:24:10.480

When I run

CODE --> T-SQL

UPDATE dbo.tblTable
SET fldDateJoined = '2017-08-14 23:59:59.998'
WHERE fldMember = 'John'

SELECT * FROM dbo.tblTable 

I get
fldMember fldDateJoined
John         2017-08-14 23:59:59.997
Joe           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.

RE: Missing Millisecond

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 f BETWEEN A AND B translate and modify that to the two conditions A<=f AND f<B (whereas BETWEEN would check A<=f AND f<=B), so B can be set up as the midnight of a day you want to exclude, also see thread183-1779551: Using GETDATE.

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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close