Press "Enter" to skip to content

Category: Dates and Numbers

Calculating Weekdays with M

Kristyna Hughes calculates weekdays on the fly:

Knowing the days between events is a fairly common reporting request because a lot of reporting is created to track SLA’s (service level agreement) and other KPI’s (key performance indicators). While getting the days between two dates is fairly easy to achieve, they tend to follow up and ask how many week days there are between two timed events. For example, one company may have a SLA to ship an order within three week days of the order being placed or else a discount is applied to the order. In this case, I would highly recommend that the company have software that calculates these days in the background and stores the actual week days between order date and ship date in a database. Unfortunately, many companies create policies like this without considering future reporting needs and these values have to be calculated on the backend.

Click through for the code but be sure to read the note that this is all weekdays, including holidays.

Comments closed

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

TRY_CAST and TRY_PARSE

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;
SELECT DATEDIFF (DAY, GETDATE (), (DATEADD (YEAR, @YearsToRetire, GETDATE ())));

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