I am going to start a series of articles (tidbits) on type conversion functions available in the Transaction SQL (T-SQL) language.
One analogy of a data type conversion is apply the correct action to change the physical form of water (H2O). You can convert water to ice by cooling it. You can convert water to vapor by heating it. However, the chemical composition of water is still the same!
Information is stored in a database as rows in a table. Each field in a table is defined with a certain data type. The original designer of the database schema might not be aware of all potential uses of the stored information.
Therefore, it is not uncommon as a developer to convert data from one type to another. Conversions can either be categorized as implicit or explicit.
The word coercion is used to denote an implicit conversion from one data type to another. This can occur either during compilation or during run time. No code is added by the developer for this to occur.
On the other hand, the word cast refers to explicitly changing the interpretation of the bit pattern representing a value from one type to another. The developer adds code for this action to take place.
Today, I want to talk about how to use CONVERT function which takes a target data type, a source data value, and a optional style parameter. It returns the the source data value casted to the target data type.
The example below converts the current date time into a date string and a time string. The style parameter is used to determine the format of the string. Last but not least, I am storing 2 raised to the 16th power as a big integer.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- -- Format expressions using CONVERT() -- -- Date as string SELECT CONVERT(CHAR(10), GETDATE(), 101) AS RIGHT_DATE -- Time as string SELECT CONVERT(CHAR(8), GETDATE(), 108) AS RIGHT_TIME -- Use large range data type SELECT CONVERT(BIGINT, POWER(2, 16)) AS LARGE_NUMBER |
The output from the TSQL snippet is shown below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
output: RIGHT_DATE ---------- 07/08/2013 RIGHT_TIME ---------- 15:33:11 LARGE_NUMBER -------------------- 65536 |
Please note the CONVERT function uses the following matrix to transform data from one form to another. The example above shows explicit, allowable conversions. One nice feature of this function is the style parameter which can provide you with differently formatted information.
Next time, I will be talking about the CAST function which does not have a style option.
