By Rudderstack Team

HowTo: SQL type casting

While in an ideal world each column in a database table has a suitably chosen data type, in this non-ideal place we live in, having stored dates in the wrong format, is a problem that the majority of those who wrote SQL has faced. This is a common consequence when different applications use the same data in different ways than the one you originally defined.

Handling columns with wrong data types can become cumbersome easily, as it does not allow you to perform comparisons or logical operations the way you may wish. So the need of casting columns into different types than the one currently defined arises.

Luckily for us, the majority of the top used databases have implemented helpful functions that one can use to perform this type of tasks easily. Unfortunately, these functions we are talking about, are not exactly the same across all database systems.

Having created a neat cheatsheet of the majority of the cases one may face, can save a lot of time and effort from searching in long documentation pages.

In this post, we are going to present the more common castings one may wish to perform along with the SQL code to do so, when using Amazon Redshift, PostgreSQL, Google BigQuery, or SQL Server.

In addition, we are going to investigate what happens in the case of fields that represent currency and what is the most appropriate way to store them in different databases.

Microsoft SQL Server

If you use Microsoft SQL Server and want to convert a value from one data type to another the two available functions that will help you are cast and convert which both provide similar functionality.

String to Date and Timestamp

The CONVERT() function takes as input the current valid expression and the target data type and performs the appropriate conversion.

Convert syntax:

CONVERT(target_data_type(length),expression,style)

Regarding the supported representation styles, MS SQL Server offers a quite large variety as shown in the following table.

String to Numeric

Regarding the conversion of a string into a numeric data type such as int and float, cast is the suitable function. Its syntax is similar to convert, taking as input the column name or the literal along with the target data type.

Cast syntax:

CAST ( expression AS data_type )

Fortunately, MS SQL Server is clever enough to raise an error when a malformed input data is given, such as “Conversion failed when converting date and/or time from character string.

Numeric to Currency

Although MS SQL server does support a special data type for currency, called money, the usual advice is to prefer the decimal data type since money is a residue of an older version of SQL Server where the bytes that a decimal number occupied were fixed to 17.

At that time, money data type reserved only 8 bytes which were obviously sufficient for currency subdivisions.

Since MS SQL Server 2005 things have changed and the user can specify the bytes that each field of decimal type occupies thus making money deprecated.

In case one wants to also include the currency sign, the format function is suitable. The following examples combine everything we have mentioned so far regarding currency.

DECLARE @Price Decimal(21,6) = 1500.50
SELECT FORMAT(@Price,'c','en-US') AS 'CURRENCY IN US Culture'
SELECT FORMAT(@Price,'c','ar-EG') AS 'CURRENCY IN LE Egypt Culture'
SELECT FORMAT(@Price,'c','ms-MY') AS 'CURRENCY IN Malaysia Culture'

Postgresql

<