Often you need to rotate a picture when you are working with digital content that will be published on different websites. You may need to adjust a photo for several reasons. The default orientation of a photo depends on how the photo was originally taken. Most mobile phones use vertical (portrait) orientation. Desktop computers, on […]
PDF File: What is a .PDF file, and how do I create it?
PDF is the acronym of “Portable Document Format”, and the format was originally designed by Adobe for printing. The format is based on the PostScript language which is supported by most printers. The internal file structure encapsulates a complete description of any texts, fonts, vector graphics, raster images, etc. You might already be familiar with […]
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 […]
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?