The day of week for a given date can in Microsoft SQL server be calculated based on the @@datefirst system variable and the datepart function in Transact-SQL. The value returned from datepart is not constant but depends on the first day of week specified by the @@datefirstvariable. In modern programming languages like C# we will get a constant value for each […]
MS SQL: Get the First Day of Week in Transact-SQL
In Transact-SQL the first day of week can be calculated based on the @@datefirst system variable. The variable will return different values depending on which language has been configured for the server. For US English the first day of week will be set to 7, i.e. Sunday, while for other regions the first day will […]
MS SQL: Store a Color (ARGB) as an Integer in Transact-SQL
The most common way is to store colors in a SQL database as a string, i.e. the HTML color code (ex. #FFFFFF). The colors can also be stored as 32 bit integers. A 32 bit color value (including alpha channel) can be calculated in the following way: @Alpha * 16777216 + @Red * 65536 + @Green * 256 + @Blue The complete user-defined […]
MS SQL: Atomic Insert or Update (UPSERT) in Transact-SQL
It is not straight forward to create an atomic insert or update statement in SQL Server, i.e. we update the row if it exists. Otherwise we insert a new row. The following statement (notice the locks) will create a more or less atomic statement: In the code above PK is the primary key that will […]
MS SQL: URL Encode a string in Transact-SQL
There is no built-in function in Microsoft SQL Server to to support URL encoding. If you want to generate URLs with arguments on the fly in stored procedures, you would have to do the URL encoding yourself. URL encoding can be implemented in several ways. Either you can create your own custom userdefined function in […]
MS SQL: Create MD5 Hash string in Transact-SQL
The non-reversable hashing algorithm MD5 is supported in Microsoft SQL Server, but is not directly accessable though a simple transact-SQL function. Normally you would store the MD5 hash value as a hexadecimal string in your database. The HashBytes function returns a binary array of hash data. To convert the binary data to hex we need to use the function fn_varbintohexstr. To […]
MS SQL: Defining Constants in Transact-SQL
Transact-SQL does not offer a good way to support constants in your stored procedures or userdefined functions. This means that you either can choose to hardcode your constants or, the more elegant way, define a userdefined function for each “constant” value. To ensure optimal performance, the schemabinding keyword should be used. Here is an example: In your stored procedure […]
MS SQL: Get the Time portion from a DateTime
To get only the Time portion of a DateTime variable in Transact-SQL, you can use the following function: Please notice that the base date is January 1st 1900. Any dates/times prior to this day won’t work, Releated How to get the Date portion of a DateTime in Transact-SQL? How to create a Date in Transact-SQL?
MS SQL: Create a Date in Transact-SQL
There is no straight forward function in Transact-SQL to create a simple date based on year, month and day. Below we have created a userdefined function to handle this. Notice that the base date in Microsoft SQL Server is January 1st 1900. We generate the date by adding the year, month and day to the base […]
MS SQL: Get the Date portion from a DateTime
To return only the date portion of a DateTime variable in Microsoft SQL server, you need to use a combination of the dateadd and datediff functions in Transact-SQL. Releated How to get the Date portion of a DateTime variable in Transact-SQL? How to create a Date in Transact-SQL?