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 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