The Bit Bucket

If you’ve done much work with Power BI, you’ll know that relative dates can be really useful. But if you’ve tried to use them, you’d also know that they aren’t as useful as they might at first seem. You configure them in Power BI Desktop and they look great. You deploy them to the Power BI Service, and you have a problem.

Why?

Because Power BI Desktop uses your local timezone and the Power BI Service doesn’t. So if the dates and/or times you have in the data you’re analysing don’t happen to be UTC based (or you live in England), you’re fresh out of luck. If you go looking for today’s data, you might well be seeing yesterday’s data.

2020-03-27

I use SQL Server Management Studio (SSMS) every day. When I first connect to a server, I’m presented with a list of servers to choose from:

Now this is really convenient, and in recent versions, it also remembers passwords for different ways of connecting. For example, I might have one server that I sometimes connect to using Windows credentials, and other times use a SQL credential for testing. It’s great that it remembers both.

2020-03-26

I recently posted about how we added Countries and Currencies to our free SDU Tools for developers and DBAs. I use those all the time in drop-down lists. But the other one that’s super-helpful, is to know which are the official currencies for each country. So we’ve added another view called CurrenciesByCountry.

It’s a simple view that returns details of the current list of official currencies for each country. You can see it here:

2020-03-25

I mentioned in earlier posts that there’s no standard way to write dates, so we end up having to write them as strings. Now that was a real problem in earlier versions where people would get that wrong.

SQL Server 2012 added an option to make that easier. DATEFROMPARTS allows you to specify a year, month, and a day to create a date, and always in that order.

Look at the following query:

2020-03-23

I know that many people found Christopher Hitchens a divisive character. Mostly that’s because he very openly, directly questioned, and at times ridiculed, long-held beliefs about religion and about inappropriately revered people. He did not (as the old saying goes) suffer fools gladly. I read Mortality quite a while ago and wanted to wait a while after his death to see how I still felt about it.

Hitchens was diagnosed with esophageal cancer in 2010 while on a book tour for Hitch-22. Hitchens died in December 2011.

2020-03-20

I regularly need a list of currencies in drop-down lists, and when performing calculations, etc. To make that easy, in our free SDU Tools for developers and DBAs, we added a tool to do just that. It’s a view called Currencies.

It’s a simple view that returns details of the current official list of common currencies.

For each currency, it returns the 3 character currency code, the name of the currency, the most common symbol that’s used for it, the name of its minor unit (like cents for dollars) if there is one, and if so, the number of minor units to every major unit (100 for cents in a dollar).

2020-03-18

It’s surprising how often you need to calculate the date for the end of a month i.e. you supply a date, and you want the date for the end of the month that contains that date. The EOMONTH function does that.

Look at the query here:

And the result here:

It might seem really easy to calculate but there are tricky aspects. For example, for a date in February, is it a leap year? You can see from the results that 2020 is a leap year.

2020-03-16

Yesterday, I posted about how to get local date and time when you’re working with Azure SQL Database. It uses UTC and there’s no option (at least not currently) to set a timezone for a database.

I mentioned that to get the local date and time, you could execute code like this:

CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Eastern Standard Time' AS datetime)

Now what many people haven’t noticed is that the time zone name can actually be a variable. (That’s refreshing because so many times in SQL Server, these things require constants). So this also works:

2020-03-13

As I work at different client sites, I see a lot of discussion about the cost of cloud-based services, in comparison with on-premises or self-hosted equivalents. One aspect that always seems to be forgotten is opportunity cost.

So many times, I see people comparing raw incremental costs of virtual machines in the different environments. Invariably they aren’t making an apples vs apples comparison. They aren’t considering staff costs, training costs, power, real estate, support costs, etc.

2020-03-12

I work a lot with Azure SQL Database, and if you’ve done that, you will have realised that, just like other Azure services, the time zone is set to UTC. Select from GETDATE() or SYSDATETIME(), and you’ll find it’s the current UTC date and time i.e. the same as you’d get from the SYSUTCDATETIME() function.

I can see why that makes sense much of the time. If your users are all over the world, that seems an entirely appropriate setting. Same deal if you’re linking different systems together: it’s good to have a common timeframe.

2020-03-12