Microsoft introduced the CURRENT_TIMEZONE() function in SQL Server 2019 for returning the time zone of the server.
More specifically, this function “returns the name of the time zone observed by a server or an instance”.
Continue readingTag: mssql
How to Delete a SQL Server Agent Job in Azure Data Studio
If you’ve installed the SQL Server Agent extension in Azure Data Studio, you will be able to see a list of SQL Server Agent jobs, as well as create, edit, view history, etc.
But you may be wondering how to delete a job.
If so, read on.
Continue reading4 Ways to Find Out What Columns will be Returned by a Stored Procedure in SQL Server
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 readingORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator (SQL Server)
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.
Rename a Primary Key in SQL Server (T-SQL)
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.
Create a Multi-Step SQL Server Agent Job (T-SQL)
In a previous article about creating a SQL Server Agent job with T-SQL, I demonstrated how to create a job with a single step.
In this article, I’ll show you how to create a job with multiple steps.
Continue readingHow to Do a LEFT SEMI JOIN in SQL Server
A LEFT SEMI JOIN is kind of a half-join. It returns any distinct values that are returned by both the query on the left and right sides of the query.
However, when using T-SQL in SQL Server, if you try to explicitly use LEFT SEMI JOIN in your query, you’ll probably get the following error:
How to Do a LEFT ANTI SEMI JOIN in SQL Server
A LEFT ANTI SEMI JOIN is a type of join that returns only those distinct rows in the left rowset that have no matching row in the right rowset.
But when using T-SQL in SQL Server, if you try to explicitly use LEFT ANTI SEMI JOIN in your query, you’ll probably get the following error:
Format sysjobhistory datetime & duration Columns in SQL Server
If you’ve ever queried the sysjobhistory table in the msdb database, you’ll probably know that the datetime and duration columns are stored as integers.
In particular, when you query this table, the run_date, run_time, and duration columns are returned as integers, which can make it difficult to read.
Below is a query you can use to return this data in an easier to read format.
Continue readingList of Date Formats Available with CONVERT() in SQL Server
The following table contains a list of the date formats that you can provide to the CONVERT() function when you convert a date/time value to a string.
These formats are provided as an optional third argument when calling the CONVERT() function. They’re provided as an integer expression that specifies how the CONVERT() function will format the date.