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

Number of Primes

Anderson's Theorem (a) The number of primes in [1,n] is no more than 2+floor(n/2). The probability of n being prime when n is not prime is 1/2 - see Dasgupta,Papadimitriou,Vazirani "Algorithms" page 26. Therefore, the E(pi(n)) is n/2. (b) There does not exist another set of adjacent primes other than {1,2,3} 5: 2 + floor(5/2) = 2 + 2 = 4:=> {1,2,3,5} : 4 <= 4 7: 2 + floor(7/2) = 2 + 3 = 5 => {1,2,3,5,7} : 5 <= 5 11: 2 + floor(11/2) = 2 + 5 = 7 => {1,2,3,5,7,11} 6 <= 7 26: 2 + floor(26/2) = 15 => {1,2,3,5,7,11,13,17,19,23} : 10 <= 15 Lagrange's Theorem is Inaccurate Lagrange's theorem about primes states that pi(x) is the number of primes <= x. The pi(x) is approximately x/ln(x). He postulated that the lim of pi(x)/(x/lnx) as x-> infinity was 1. This is incorrect. if the number of primes is bounded by n/2 then refactoring and reducing Lagrange's Theorem results in the lim of ln(x) as x approaches infinity. This is alwa...
Read more

How To Cancel ATT Uverse

I was a subscriber to the AT&T Uverse service for a little over 2 years. In that time, we had experienced good service for the first year, and then it sucked. After 12 months, or there in, the service degraded quickly, and would stop working all together at times. At first it would die for a short period of time, usually when we were not home. Then it would get progressively worst, until there was an entire week of no service. We had technicians at the house trying to fix the service, but it would repeat the behavior quite consistently. On January 15th we finally gave up and switched to a lesser service, COX TV and Internet. In the past we had cable service and it was always reliable, but not as good as the AT&T digital service. COX doesn't have nearly as many HD channels, but that's not enough. We needed internet to be reliable, and AT&T couldn't deliver that. Cancelling the AT&T service was a nightmare. Try to find anything about such things on their web s...
Read more

A Self Defeating Race False Narrative

2020 is the year of the pandemic. The SARS-Cov-2 (Covid19) virus has rampaged across the planet infecting 4,893,136 [1] people by May 20, 2020. At this time, of those 4.8M people, 323,256 people have perished from complications that arise from the infection. Arising out of this pandemic has been a narrative about non-white ethnic groups being disproportionately affected by the infection [6,7,8]. A narrative that conditions people to believe that they are perpetually victims only creates a "collective victimhood" [4,5] in that group. This "collective victimhood" costs its members millions in unrealized potential, sends them cowering from social interactions that would otherwise benefit them, and ultimately creates an environment that perpetuates itself. Let's try to dispel that false narrative and deal just with data. I pulled my data from the CDC [9] looking at mortality only. The mortality data from CDC contains per-state mortality rates on a per-infectio...
Read more