The Bit Bucket

I like to have my databases at the same database compatibility level as the server, whenever possible. But how do you know the maximum value that’s allowed? We recently added a tool to our free SDU Tools for developers and DBAs to solve this. It’s called ServerMaximumDBCompatibilityLevel.

It’s a simple scalar function that returns the maximum DB compatibility level that’s supported by the server. It takes no parameters.

If you want to know which server version (like 2017 or 2019) that the DB compatibility level represents, you can also combine it with our SQLServerVersionForCompatibilityLevel function.

2020-04-01

There’s another annoying trend that I want to call out. Every single day, I receive emails like this:

I’m sure others get them too. Now I’m sure Tatyana is a lovely lady who’s just trying to do her job and struggling to find business for her company. But I haven’t the slightest interest in what she’s offering.

Now I used to respond to these, and just say “No thanks” or “No interest”.

2020-03-31

The datetimeoffset data type was added in SQL Server 2012 and allowed us to not only store date and time values, but to also store a time zone offset (from -14 hours to +14 hours). When you’re using this data type though, you often have the datetime value and the offset separately, and need to combine them together to make a datetimeoffset value.

The TODATETIMEOFFSET function takes a datetime2 value (higher precision datetime) and a time zone offset, and returns a datetimeoffset data type.

2020-03-30

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