Category: Microsoft SQL Server

Microsoft SQL Server

MS SQL: Get the Day of Week in Transact-SQL

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 […]

Microsoft SQL Server

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 […]

Microsoft SQL Server

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 […]

Microsoft SQL Server

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 […]

Microsoft SQL Server

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 […]

Back To Top

Subscribe to our newsletter