Skip to main content

TSQL SmallDateTime vs DateTime

Today I noticed an odd little quirk about TSQL (Microsoft SQL) and SQL Server. If you have a smalldatetime, you only record date + hours + minutes. To get seconds, millis, and such, you need the full datetime.

Well, if you are using "getdate()" to compare against the smalldatetime, then know that smalldatetime rounds the seconds UP to the next minute. The smalldatetime on 12:36:24.00 is actually 12:37:00.00.

This caught me today in a query where I was doing a select looking for a record that occured after the last insertion time (smalldatetime) using "getdate()". That was always returning empty because the getdate() to smalldatetime conversion was giving me a date that was in the future thanks to the round-up behavior.

To work around it, I had to convert(smalldatetime,getdate()) and then compare against my smalldatetime values.

If you are inserting records with smalldatetime and using getdate() as the smalldatetime value, then those records will be recorded up to 1 minute into the future. Be wary.

Popular posts from this blog

THE RISE OF FASCIST SOCIAL MEDIA

The Merriam-Webster dictionary defines fascism as: a tendency toward or actual exercise of strong autocratic or dictatorial control .  The phrase "dictatorial control" is important for the case that I am going to make about fascism in social media. The word "dictatorial" means "of or relating to a dictator," and a dictator is "one ruling in an absolute and often oppressive way." In 2020, social media has seen a rise in the number of autocratic events of censorship. The two social media outlets that I am going to focus on are Facebook and Twitter.  Background Facebook is a semi-private curated blogging platform where you, the user, share information at your leisure. The public part of Facebook is in Facebook Groups. With a group, outside people who are not privy to your "Facebook Wall" will join your group and establish a communal discourse. This can be private, by invitation only, or public. The Facebook is auth-walled so that you must ...
Read more

Clustered Foolishness

I had morning coffee with a well respected friend of mine recently. Aside from chatting about the usual wifery and family, we touched on the subject of clustered indices and SQL Server performance. A common misconception in the software industry is that a clustered index will make your database queries faster. In fact, most cases will demonstrate the polar opposite of this assumption. The reason for this misconception is a misunderstanding of how the clustered index works in any database server. A clustered index is a node clustering of records that share a common index value. When you decide on an index strategy for your data, you must consider the range of data to be indexed. Remember back to your data structures classes and what you were taught about hashtable optimizations. A hashtable, which is another way of saying a database index, is just a table of N values that organizes a set of M records in quickly accessible lists that are of order L, where L is significantly less than M. ...
Read more

Trademarks In The Dark

If you have a business, then you know that filing for a trademark is pretty easy in the USA. You just go to the USPTO web site ( www.uspto.gov ) and start filling out the form. The cost is significantly less now, nearly a third of what it was a couple of years ago. That's great news. What you don't know about your mark, though, is that there is a plethora of common law that dictates whether or not you can file with your specimens. The specimens are documents that clearly show your mark being used in commerce. Well, my last mark registration came back to me with the examiner asking for a better specimen that places the mark in closer proximity to evidence of commerce. Closer proximity. Yeah. Right. Apparently Lands’ End, Inc. v. Manbeck, 797 F. Supp. 511, 514, 24 USPQ2d 1314, 1316 (E.D. Va. 1992); In re Dell Inc., 71 USPQ2d 1725, 1727-1729 (TTAB 2004); In re MediaShare Corp., 43 USPQ2d 1304 (TTAB 1997); TMEP §§904.06(a) and (b), establish some common law that determines an acce...
Read more