Data Type Conversions In 4 Database Systems

Kevin Feasel

2017-11-20

Syntax

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

Multiple Mentions Of A Column In An UPDATE Statement

Doug Lane walks us through various scenarios with updates including the same column multiple times: An application developer came to me with this question recently: “Can I use the same column twice in a SQL UPDATE statement?” Yes and no. It depends on what you mean by “use”. Read on to see what Doug means.

Read More

Using AT TIME ZONE In SQL Server

Randolph West looks at the AT TIME ZONE clause when working with a specific time zone in SQL Server: The time zone name is taken from a list maintained in the following Windows registry hive: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones Note: For SQL Server on Linux (and Docker containers), a registry shim performs the same function as the Windows registry […]

Read More

Categories

November 2017
MTWTFSS
« Oct Dec »
 12345
6789101112
13141516171819
20212223242526
27282930