Press "Enter" to skip to content

Category: Dates and Numbers

Rounding Differences in Power BI

Marco Russo explains the importance of data types for rounding in Power BI:

In one of the last classrooms I delivered, students were wondering why the results of their formulas were close but not identical to the proposed solution. We quickly identified the problem being an issue of data type conversion already covered in Understanding numeric data type conversions in DAX. However, the issue is interesting as a simpler example to show that different DAX calculations can produce different results because of a different way of rounding numbers!

Read on for Marco’s example.

Comments closed

Time Zones and Extended Events

Tomas Zika answers a question:

I’ve helped answer another question that appeared on the SQL Server Slack:

Are timestamps in XE event files you view in SSMS local or server time?

To test this, I need a server in a different timezone than the client (SSMS). I find the quickest and most easy tool for that to be containers – more specifically, Docker.

Click through for the answer, as well as a few Docker-related incidentals.

Comments closed

SQL Server and Daylight Savings Time

Joe Pollock has to turn the clocks forward:

At the start of Daylight Saving Time (DST), which is this weekend in the UK, the clocks will move forward by one hour at 1am. Apart from the fact that we all lose an hour of our night’s sleep, what effect will this have on your scheduled jobs in SQL Server?

As we saw in my last blog post, we know that the SQL Server engine always carries on regardless of when the clocks change, it knows that this has happened, but nothing unusual occurs in the engine itself. However, SQL Server Agent, which runs scheduled tasks, is not the same, as this directly impacts how it works. 

Read on to see what can happen. Also, this can be relevant when you have unique key constraints on datetime values.

Comments closed

Dealing with Shift Times

Kenneth Fisher knows what time it is:

One of the more interesting jobs I’ve had over the years was for a company that created emergency room software. It was pretty cool software and I learned a lot, both about writing queries in SQL Server and about how a software company can be run. One of the more interesting things in the various reports we created was the concept of shift calculations. In other words, what happened during a given shift.

I’ve had to do something similar (though it was for nurse scheduling rather than emergency rooms). Things get really tricky when you start dealing with 12-hour and 16-hour shifts, tracking overtime, and the like.

Comments closed

Time and Unit Tests

Michael J. Swart says, look at the time!:

A flaky test is a unit test that sometimes passes and sometimes fails. The causes of these flaky tests are often elusive because they’re not consistently reproducible.

I’ve found that unit tests that deal with dates and times are notorious for being flaky – especially such tests that talk to SQL Server. I want to explore some of the reasons this can happen.

As a quick note, if you’re using time in database unit tests, don’t use GETUTCDATE() or GETDATE() or any other function like that. It’s a non-deterministic function. Instead, use specific dates and times. That way, you can explicitly test for the types of things Michael points out.

Comments closed


Joe Obbish shows the difference between two functions:

There’s a lot of guidance out there that states that TRY_CAST is a faster, more modern version of TRY_PARSE and that TRY_PARSE should only be used if you need to set the optional culture parameter. However, the two functions can return different results in some cases, even without the culture parameter.

That guidance is blatantly wrong. TRY_CAST() and TRY_PARSE() both came out in SQL Server 2012. TRY_PARSE() uses .NET to perform parsing, which is going to have some edge case differences, especially around cultures and localization. TRY_CAST() is CAST() in an error-safe wrapper. If anything, TRY_CAST() is the “old” version and TRY_PARSE() the “new” version, with scare quotes in place because they both came out at the same time.

Both of them are useful, though I do agree with Joe’s advice of avoiding TRY_PARSE(), at least for larger datasets. If you’re parsing a single date or a small table of dates, TRY_PARSE() does an excellent job because TRY_PARSE('13/01/2019' AS DATE USING 'fr-fr') is not something you can easily do with TRY_CAST() in a US locale.

Comments closed

Date Math in Powershell

Steve Jones adds 12 years in Powershell:

I saw a fun post on Twitter recently asking days until retirement. I wrote this code:

DECLARE @YearsToRetire INT = 11;

I thought that wasn’t bad, but then I wondered, how would I do this in PowerShell? I knew there had to be a way, so I googled and ran into this article.

Normally I need to take off my shoes to add that many years.

Comments closed

BETWEEN and Overlaps

Chad Callihan reminds us that BETWEEN is inclusive of both sides:

Thanks to Robert for his comment on the last post that then spawned this post. In the example about sargable dates, I thought I would go with the more simple look and only use dates instead of adding the times. The point is to look at sargability, right? Well, here’s an example on why you don’t mix and match dates and datetimes.

Click through for the demonstration.

Comments closed

Sargability and Dates

Chad Callihan makes me want to change the title to “Getting Sarge a Date”:

We’ll use the StackOverflow2013 database for this example. Let’s say we want to return the users created in 2013. One way to return this data is to use the YEAR() function to pull out the desired year for our query:

For the reference, check out Chad’s prior post. My expectation is that about 90% of people in the US who are aware of the term pronounce it “Sarge-able” instead of “Sar-guhble” and therefore immediately think of Sergeants.

1 Comment