1

I've came across an odd problem while comparing dates (actually smalldatetime) in SQL Server. It seems like there is a 30 seconds offset/bias while comparing dates close to the end of the day:

This is correct:

enter image description here

But this is definitely not correct:

enter image description here

What is happening here? Is math broken?

Pedro Henrique
  • 680
  • 7
  • 22

2 Answers2

4

The smalldatetime type has a accuracy of one minute so the literal '2019-12-31 23:59:30' is rounded to '2020-01-01 00:00:00'.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
4

If you read the [documentation][1] for smalldatetime, you will see this note:

https://learn.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql?view=sql-server-2017

Accuracy One minute

In other words, the literal value is being rounded. As a result, what-you-see-is-not-what-you-get.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Right, try to enter data in the table with smalldatetime: one value with seconds, one without. It won't let me add the value with seconds. – Steve May 24 '18 at 20:41