Category: SQL Server

Change the Date Format For the Current Session in SQL Server

Posted on by Ian

Whenever you connect to SQL Server, a bunch of default settings are applied to your session. These include the language and the date format settings.

The date format is normally determined by your default language. For example, if your default language is us_english, then the default date format will probably be mdy, and the first day of the week will be day 7 (Sunday).

If you change your language, you the date format will implicitly be updated accordingly.

However, you still have the option of changing the date format without changing the language. To do this, you can use SET DATEFORMAT.

Continue reading →

Convert a Date to Another Time Zone in SQL Server

Posted on by Ian

The AT TIME ZONE clause was introduced in SQL Server 2016 to convert a date into a datetimeoffset value in a target time zone.

This function is similar to some other T-SQL functions, such as SWITCHOFFSET() and TODATETIMEOFFSET(), however, the AT TIME ZONE clause allows/(requires) you to specify the time zone offset by name, instead of an actual offset value.

This article explores how AT TIME ZONE works, and explains its benefits when compared to the other functions mentioned.

Continue reading →

4 Ways to Find Out What Columns will be Returned by a Stored Procedure in SQL Server

Posted on by Ian

If you ever find yourself in the situation where you’re about to execute a stored procedure, but you suddenly catch yourself. You wonder “How many columns does this thing return? Which tables? Does it query a remote server?”

The good news is there are several ways to get this information before you run the query. Let’s look at them.

Continue reading →

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator (SQL Server)

Posted on by Ian

If you’re running a query in SQL Server, and you get the following error…

Msg 104, Level 16, State 1, Line 8
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

…you should check your SQL statement – you’ve probably omitted a column from your SELECT list.

As the error message implies, you’ll probably only see this error if you’re running a query that contains a UNION, INTERSECT or EXCEPT operator.

Simply adding the column to your SELECT list should fix the problem.

Continue reading →

Rename a Primary Key in SQL Server (T-SQL)

Posted on by Ian

In SQL Server, you can use the sp_rename stored procedure to rename a user created object in the current database, including a primary key.

This can be handy if you’ve got a primary key that had its name automatically assigned, and you now want to give it a more readable name.

When you create a primary key without explicitly providing a name for it, SQL Server automatically delegates a name for it. Such names typically include a long numeric suffix, which makes it harder to remember. If you need to refer to that primary key (e.g. in your code, documentation, etc), such names can make your life more difficult. Fortunately, sp_rename provides a quick and easy way to change this name.

Continue reading →