Bug Report #1 - GETUTCDATE() In Longer Queries
Tags: bugreport, sqlserver, getutcdate, post
Published: 2019-01-20 15:18:08

---
title: Bug Report #1 - GETUTCDATE() In Longer Queries
published: true
tags: bugreport, sqlserver, getutcdate, post
canonical_url: https://horia141.com/bug-report-1.html
series: "Bug Report"
---

This is the first in a series of articles about bugs I encounter while working[1]. It’s a new thing I’m trying for the blog, along side the Friday Blast series, which is currently at [week 4](http://horia141.com/friday-blast-4.html). There’s a lot of bugs out there. But I want to highlight those which are _generic_ enough so that the solution is useful to other people, and _fun_ enough that they’ll be worthwhile to write about.

Today’s bug is related to [`GETUTCDATE()`](https://docs.microsoft.com/en-us/sql/t-sql/functions/getutcdate-transact-sql), the SQL Server function, and how I was using it in a daily scheduled task at StackOverflow. More or less this task looked at active ads campaigns which were more than two weeks past their expiration date. Once found it _archived_ them, which basically meant setting their _ArchivedDate_ field to the current time, _and_ inserting an _Archived_-type event in an events log. The naive way I was coding this up looked more-or-less like this:

```
insert into CampaignHistory (CampaignId, Timestamp, HistoryType)
select Id, GETUTCDATE(), 6 /* Archived */
from Campaigns
where ArchivedDate is null
and DATEDIFF(day, EndDate, GETUTCDATE()) > 14;

update Campaigns
set ArchivedDate = GETUTCDATE()
where ArchivedDate is null
and DATEDIFF(day, EndDate, GETUTCDATE()) > 14;
```

This query is a faithful translation of the English prose from above. It gets executed as given via [Dapper](https://stackexchange.github.io/Dapper/), the micro-ORM we use.

I was pretty sure `GETUTCDATE()` would be the same value across the whole query, since I knew it would be the same inside a single query like the first `insert`, even though multiple rows would be affected. But it turns out that’s not the case and the values returned in the two queries are different.

What this means is that the set of rows which are produced by the `select` in the first query, may be different than the one in the `update` query. More precisely, they’ll be a subset of them, as enough time might pass between their execution, that campaigns which were not two weeks past their end date when the first query was executed, became so when the second query was executed.

So we ended up with a bunch of Campaigns which were archived, yet had no corresponding Archive event. Once detected, and once I figured out this issue, the fix was pretty straightforward. I just had to use a deterministic value for the date used for all comparisons, so the two row-sets were identical. The code simply became:

```
declare @cutoff datetime = GETUTCDATE();

insert into CampaignHistory (CampaignId, Timestamp, HistoryType)
select Id, @cutoff, 6 /* Archived */
from Campaigns
where ArchivedDate is null
and DATEDIFF(day, EndDate, @cutoff) > 14;

update Campaigns
set ArchivedDate = @cutoff
where ArchivedDate is null
and DATEDIFF(day, EndDate, @cutoff) > 14;
```

* * *

[1] At some point, I’m probably going to post pictures of bigger insects I encounter as an in-joke to this 0 person community.