The Bit Bucket

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 recently wrote about the need for T-SQL to have constants. I received a lot of feedback on that. Everyone seemed to agree, although one or two asked “why bother as Microsoft isn’t really listening now about enhancing T-SQL?”. I understand that sentiment but I don’t think things are quite as bleak as some are suggesting. I currently sense a stirring within the product group, where there is a new interest in developers.

2020-03-19

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

I regularly need a list of countries in drop-down lists, 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 Countries.

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

For each country, it returns the 3 character country code, the country number, the name of the country and which continent it’s part of.

2020-03-11

I was working with Azure Analysis Services the other day, and was having an issue with the processing of a tabular data model. When I first tried to process, I was receiving an error that told me pretty clearly that the AS server couldn’t connect to the SQL Database. Fair enough. Wasn’t sure what had nuked those credentials but reset them.

Next, I started to get this error: Failed to save modifications to the server. Error returned: ‘The key didn’t match any rows in the table

2020-03-10