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.