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 →

5 Ways to Get the Hour from a Date in MongoDB

Posted on by Ian

MongoDB provides a good range of aggregation pipeline operators for working with dates, including operators that extract certain parts of dates, such as the year, month, day, hours, minutes, etc.

There are also a couple of MongoDB methods that enable you to iterate through a cursor, and apply a JavaScript function. You can therefore use JavaScript to extract date values and date parts, etc from a field as required.

This article presents 5 ways to return the hour portion from a Date object in MongoDB.

Continue reading →