Sql-Server

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

Hi Folks,

Just a heads-up that SQL Down Under podcast show 78 with Microsoft Program Manager Pam Lahoud is now out the door. Wasn’t planned but it ended up being great timing for International Women’s Day as well.

In the show, Pam discusses index operations in SQL Server, from standard operations through to the resumable operations that have recently been added and optimized.

You’ll find the show (and podcast subscription details if you want those) here: http://podcast.sqldownunder.com

2020-03-09

In an earlier post, I showed how you could extract the year, month, day, etc. from dates. But these came back as numbers. So instead of “March”, I got 3 returned.

Sometimes though, I want the name of the period, not the number.

Look at this query:

And you can see what it returns here:

This looks easy, but what if I’m not speaking English? It’s important to understand that functions like DATENAME are affected by the regional/language settings that you are currently using in your session.

2020-03-09

In “Progamming 101”, developers learn that sprinkling hard-coded numbers and strings throughout their code, is a really lousy idea. Yet I regularly see T-SQL code that’s littered with hard-coded numbers and “special” string values. I really wish we could avoid that. It would greatly improve code quality, and improve debugging for procedures.

Take the following code as a really simple example:

SET @NextInterval = @CurrentInterval * 1440;

It’s not at all obvious why the value 1440 is there. In this case, it was the number of minutes in a day. Today, we can do this:

2020-03-05

I often need to calculate a day number in a month. For example, I need to work out the 3rd Tuesday of a month, or the 4th Friday, and so on. To make that easy, in our free SDU Tools for developers and DBAs, we added a tool to do just that. It’s a function called DayNumberOfMonth.

It’s a function that takes four parameters:

@Year is the year (of the month in question) @Month is month in question (as a number within a calendar year i.e. March = 3) @DayOfWeek is an integer with Sunday = 1, Monday = 2, etc. @DayNumber is also an integer for the day number (i.e. 3 for 3rd Monday)

2020-03-04