TSQL provides the DATEPART() function, which enables us to return the day of the year for a given date in SQL Server.
By “day of the year”, I mean the day number of the given year.
t-sql
How to do an INSERT Pass-Through Query in SQL Server
In SQL Server, you can use OPENQUERY to execute a pass-through query on a linked server.
OPENQUERY is commonly referenced in the FROM clause of a query as if it were a table, but it can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement.
This article presents an example of using OPENQUERY to do an INSERT pass-through query.
Replace a String with another String in SQL Server (T-SQL)
In SQL Server you can use the REPLACE() function to replace all occurrences of a string with another string.
The function accepts three arguments; the string that contains the string to be replaced, the string to be replaced, and the string to replace it.
Convert a Month Name to the Month Number in SQL Server (T-SQL)
In SQL Server, you can use the MONTH() function to convert a month name to its corresponding number.
Insert Characters into the Middle of a String in SQL Server (T-SQL)
SQL Server provides the STUFF() function that enables you to insert a string inside another string.
The function also allows you to specify the number of characters (if any) to delete from the original string in order to fit the new string.
3 Ways to Extract the Year from a Date in SQL Server (T-SQL)
There are several ways to return the year from a date in SQL Server. Here are three (or is it four?).
2 Ways to Get the Default Language of a Login in SQL Server (T-SQL)
There are (at least) a couple of ways you can use T-SQL to return the default language of a specified login in SQL Server.
In particular, you can use the following methods:
- Query the
LOGINPROPERTY()function. - Query the
sys.server_principalssystem catalog view in themasterdatabase.
Examples of these are below.
How to do an UPDATE Pass-Through Query in SQL Server
In SQL Server, the OPENQUERY rowset function enables you to execute a pass-through query on a linked server.
OPENQUERY is commonly referenced in the FROM clause of a query as if it were a table, but it can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement.
This article presents an example of using OPENQUERY to do an UPDATE pass-through query.
How to Reference JSON Keys that Contain Special Characters when using OPENJSON, JSON_QUERY, and JSON_VALUE (SQL Server)
If you’re using a T-SQL function such as OPENJSON(), JSON_QUERY(), or JSON_VALUE(), you might be wary of any non-alphanumeric characters that might be in the JSON document that you’re working with. Especially if those special characters are in the key names, and you need to reference those key names.
OPENROWSET does not accept variables for its arguments (SQL Server)
If you’re trying to use OPENROWSET to run an ad hoc distributed query in SQL Server, but you keep getting an “incorrect syntax” error, and you just can’t see anything wrong with your syntax, it could be that you’re trying to use a variable for one of the arguments.
As mentioned in the title of this post, OPENROWSET does not accept variables for its arguments.
If you’re using variables as arguments, try changing these to string literals.