The Bit Bucket

I mentioned in the last T-SQL post how you could create a datetimeoffset value by combining a datetime2 with an offset. But sometimes you need to switch to a different offset. And that's what the SWITCHOFFSET() function does:

In this query, I was taking a local time (that had a timezone offset of 11 hours), and switching it to the current time in Seattle (with a -7 hours offset):

So this function allows us to take a datetimeoffset value and convert it to a different offset.

Keep in mind that even though we have handling of timezone offsets, SQL Server isn't timezone aware to any great deal. However, there is a function sys.time_zone_info that will tell you the current offsets and daylight savings settings for different timezones.

What it doesn't do is provide you with future or historical timezone info.

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.

It's great to have SQL Server data types now that handle time zone offsets. Sometimes though, you need to combine a datetime value and an offset to produce one of these new values. That's what the TODATETIMEOFFSET() function does:

In this example, I've taken 28th February 2019 and added a timezone offset of 10 hours to it:

the output data type is datetimeoffset.

The data type of the first value is actually datetime2.

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 previous T-SQL posts that one of the challenges is 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. The DATEFROMPARTS() function added in SQL Server 2012, though, can help with this:

This query takes the year 2019, the month 2, and the day 28 and returns a date:

All of the date related data types have options with FROMPARTS. So for example there's a DATETIMEFROMPARTS(), a DATETIMEOFFSETFROMPARTS(), etc.

Sadly, though, at the time of writing, the Intellisense for these in SSMS is very poor. Currently it doesn't tell you what each parameter is. That's not good.

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.

When writing T-SQL, it's surprising how often you need to calculate the end of month for a given date, and so you want the last date in the month for whatever date you supply. That's the main thing that the EOMONTH() function does.

So in this case, 16th of February 2020, we might wonder if that is a leap year. So would it be the 28th or the 29th? And so if we execute that, we find the 29th is the date, so this is actually a leap year:

Now this function can also take an optional second parameter. If you put the value 1, it will give you the end of the month after the date you supply. A value of 2 will give you the end of two months later. And so on. You can even give it negative values. That's actually quite useful.

There are two other comments I'd make on it though:

There's no equivalent BOMONTH (beginning of month). I think that's really a little strange.

The other thing is I actually don't like the name of the function at all. I'd really prefer it was ENDOFMONTH (or even END_OF_MONTH -> that's another thing the SQL Server team have never been consistent on. Some functions and object names have underscores between words, some don't).

There's no rational argument for saving those three characters, and for me, it's just an unnecessary abbreviation. I'm told the reason they did that is that's the name of the function in Excel. That doesn't sound a great reason to me and look, I'd encourage you, whenever you're writing code yourself, spell out names in full unless there's a really strong reason to abbreviate them.

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've shown in recent posts how to extract components out of dates and times. You might also want the name of a month or a day, rather than just its number. The DATENAME() function does that.

In this query, I've asked for the name of the month that's part of the date 28th February 2019. So it returns:

An important concept here though is that this is based upon my regional settings. Mine currently are set to US English, to it returned February. But if I had different regional settings, it would display the name in whatever region I was configured to use. So if I had a French configuration that would come back with Février 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.

In previous posts, I mentioned that you can easily extract the year, month, and day from dates and times, but you might want other components. That's what the DATEPART() function does.

You can see the output from the query above here:

To use this function , we put the interval that we're after and then the value we want to find it out from. So if you look at this one, I said I wanted the day from 28th of February 2019. The day is 28.

If we look at second example, I had a date there, but I had no time shown at all, so the second comes up as zero.

In the third example, I did have seconds shown, so that was 11 hours, 59 minutes and 46 seconds. It's then extracted the 46 seconds out.

It's a useful function.

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.

Another question that I keep seeing on SQL Server forums is:

Why do my SQL Server backup files keep getting much larger?

The backup commands that the poster is using look like this:

When you do a backup, what you're actually doing is adding a backup set to a media set. Each time you do a backup like that, you're adding another backup set to the same media set.

So, what the poster is missing is that the default for SQL Server backups, is to APPEND to the operating system file. So more and more backups are now being included in that file.

If you want the file to only include the single backup, and overwrite what's already there, you can do this instead:

INIT overwrites the backup file but keeps the media header. FORMAT overwrites the media header as well.

 

 

When you're writing T-SQL code, it's also common to need to work out how far apart two dates are, or it could even be dates and times.

The DATEDIFF() function does that. In this case I'm asking how many days is it from 28th of February 2019 to 31st of July 2019?  The answer is:

So it says the difference is 153 days. Now we could have used any of those other intervals for that. I mentioned them before in this post.

I could work at how many minutes that was or how many seconds that was between the two times. I could even ask how many months it is.

The first date that we pass is the from date or the from starting point. The second one is the ending time. So if I put those around the other way it will also work it out backwards, but I would see -153.

Finally, it might occur to you that if I asked for nanoseconds over a long period, a very big number would come back. It could be too big for an integer. So to get around that, there's also a DATEDIFF_BIG() function that does the same thing but returns a bigint 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.

I answered a forum question the other day about how to list all the columns in a SQL Server database. That's straightforward enough, but they also asked for all the extended properties for the column.

In case you need to do this, here's some code:

How it works

I start with the sys.columns view and join it to sys.tables and sys.schemas, to get the schema and table name. The other reason is to make sure it's not a Microsoft-supplied table. I also wanted to exclude the sysdiagrams table that is created when you first create a database diagram using SQL Server Management Studio. (Curiously, that one's not flagged as a Microsoft-supplied table).

The next join was to sys.types to get details of the data type. Notice that the join is on both system_type_id and user_type_id. You need to do that to allow for aliased data types. If you want the underlying base-type, change the ON clause to this:

While the first query would return names like sysname, the alternative ON clause would return same column as the underlying type like nvarchar(128).

Finally, I'm outer applying a query that finds the extended properties for a column. The sys.extended_properties view provides that. It can work with many different types of objects, so you need to closely define what you're looking for. The major_id is the object (i.e. the table) and the minor_id is the column_id. I've used an OUTER APPLY to make sure the column comes back, even if it doesn't have any extended properties.

If you only want columns with extended properties, you could change the OUTER APPLY to a CROSS APPLY instead.