Data Type Conversions In 4 Database Systems

Kevin Feasel



Eleni Markou has samples for converting strings to dates, numerals, or currency in SQL Server, Postgres, Redshift, and BigQuery:

The TO_DATE function in PostgreSQL is used to converting strings into dates. Its syntax is TO_DATE(text, text) and the return type is a date.

In contrast with MS SQL Server which has strictly specified date formats, in Redshift, any format constructed using the patterns of the table found in the corresponding documentation can be correctly interpreted.

When using the TO_DATE() one has to pay attention as even if an invalid date is passed, it will convert it into a nominally valid date without raising any error.

There are a few other tricks in SQL Server for some of these (for example, on 2012 or newer, I’d use TRY_CONVERT rather than CONVERT).  That said, it’s a good overview of how to translate skills in one relational system to another.

Related Posts

Strings And Identifiers

Kenneth Fisher explains the difference between a string and an identifier: A common mistake, and one I make frequently myself is to use a string in place of an identifier, or vise-versa. So to start, let’s have some definitions, shall we? String a linear sequence of characters, words, or other data. Identifier a sequence of […]

Read More

Identity Columns And Linked Servers

Kenneth Fisher points out an oddity when inserting data across a linked server into a table with an identity column: So far so good. Now let’s throw in a twist. Let’s call it through a linked server. INSERT INTO [(local)\sql2014cs].Test.dbo.IdentTest VALUES ('Col1','Col2'); Msg 213, Level 16, State 1, Line 4 Column name or number of […]

Read More


November 2017
« Oct Dec »