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

Look at the following query:

The date of 28^th February 2019 has the offset of +10 hours added on. You can see the result here:

 

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

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:

You can see the result returned here:

Now apart from dates, of the date related data types have options for using FROMPARTS (e.g. DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, etc.)

One poor aspect of these in SQL Server Management Studio though, is that the Intellisense is currently lousy:

Surely that isn't necessary, and correctly named parameters could be provided.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

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

You can use our tools as a set or as a great example of how to write functions like these.

Find out more

You can see it in action in the main image above, and in the video here:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

 

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.

The EOMONTH function can do more than this. If I supply a second optional parameter, I can find the end of the month for surrounding months. If I say

SELECT EOMONTH('20200216', -1)

I will see the end of the previous month. A parameter of 1 would give the next month. A parameter of 2 would give the end of April.

One final note is that I don't like the name of this function. I really with it just was ENDOFMONTH. There's no need to make it less clear by saving those 3 characters. There are many other functions that have very long names, so why shorten this one?

I'm told that the reason they did this is that that's the name of the function in Excel. To me, that's a poor reason. I'd encourage you that whenever you're writing code yourself, that you spell out names in full unless there's a strong reason to abbreviate them. (And that's rare).

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

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:

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:

Note: sysname is the standard data type for system objects and it's the datatype that's used for these time zones. I found that out by looking at the datatypes returned by querying the sys.time_zone_info view. sysname is currently mapped to nvarchar(128).

Now this means that I could also make the timezone be a value that I retrieve from the database.

Session or Database Level

The pain with that option is that I'd need to retrieve it in every piece of code that needs the current date and/or time. But there is another way around that.

We can store values at the session level, that are present for the entire session. So in my application, I could execute the following code every time that someone logs on:

Then later we can just use that value whenever we need the current date and time:

And of course if you just want the date, change the final cast to a date, not a datetime.

It's a lot wordier than GETDATE() or SYSDATETIME() but at least it works.

 

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.

However, if all your users are in Sydney, suddenly that seems to be a problem.

I wish there was a way to set the timezone for an Azure SQL Database but currently, there isn't.

sys.time_zone_info

In SQL Server 2016, we saw Microsoft add a new system view.  It's called sys.time_zone_info. You can read about it here. If you query it, you can see what it does:

The output is shown here:

It has time zones and their current UTC offset, and also lets you know if the time zone currently has daylight saving time.

Note that this isn't giving you historical info. That's a much harder problem. It's just the value at the current time.

Current Local Time

Now in theory, we could use this to get the current local time for a given timezone:

The challenge with this, is it's way, way too slow. I've also found it leads to really poor estimates in query plans. Now while the SQL Server team has done great work with T-SQL scalar functions lately, this function would still have performance issues, and restrict parallelism. Based on that, I wouldn't recommend calling it for every row in a large rowset.

AT TIME ZONE

So let's look at a faster option. If you are comfortable with including a larger (and less obvious) expression directly into your code in place of GETDATE() or SYSDATETIME(), you can create a better performing expression by using the AT TIME ZONE modifier that was added in SQL Server 2016. It's documented here.

You can use SYSDATETIMEOFFSET() to retrieve the current date, time, and offset at the server, and then use AT TIME ZONE to change the value to the target time zone. Then, because you no doubt want a date, a datetime or datetime2 value (rather than a datetimeoffset), you'll need to CAST it to what you need.

For example, if you want the current date and time as a datetime value like GETDATE() does, do this:

If you want the current date and time as a datetime2 value like SYSDATETIME() does, do this:

And if you just want the current date, you can do this:

I know it's wordier and far less obvious to someone reading the code than the function, but it will perform better. So if performance matters, this will be a better option. I hope that helps you to get around this issue.

 

 

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.

You can use our tools as a set or as a great example of how to write functions like these.

Find out more

You can see it in action in the main image above, and in the video here:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

 

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

 

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.

If I had Spanish settings, I'd see the name in Spanish instead.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.