The Bit Bucket

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 might need to change a value from one time zone offset to another.  That’s the purpose of the SWITCHOFFSET function.

Look at the following query:

SYSDATETIMEOFFSET is being used to return the current date, time, and time zone offset for the server, but we’re also asking for the equivalent with a +7 time zone offset. (That was the current time in Seattle when the query was run). You can see the result here:

2020-04-06

Hi Folks,

Just a heads-up that we’ve just released SQL Down Under podcast show 79 with Microsoft Principal Program Manager Mark Brown.

Mark is an old friend and has been around Microsoft a long time. He is a Principal Program Manager on the Cosmos DB team. Mark is feature PM for the replication and consistency features. That includes  its multi-master capabilities, and its management capabilities. He leads a team focused on customers and community relations.

2020-04-04

A simple question came up on a forum the other day. The poster was trying to work out why he couldn’t find square brackets (i.e. [ ] ) using LIKE in T-SQL.

The trick is that to find the opening bracket, you need to enclose it in a pair of square brackets. But you can just find the closing one directly.

Let’s see an example. I’ll create a table and populate it:

2020-04-02

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