In PostgreSQL, you can use the OVERLAPS operator to test for overlapping time periods.
The function returns true when two time periods (defined by their endpoints) overlap, and false when they do not overlap.
Continue readingHow to Format Numbers as Currency in PostgreSQL
To format a number as currency in Postgres, you can either convert it to the money data type, or use to_char() to convert it to text that includes the appropriate currency symbol.
This obviously assumes that the number isn’t already stored using the money type.
Below are examples of each of these options.
Continue readingHow to Pause the Execution of a Statement in PostgreSQL
PostgreSQL includes three functions that allow you to delay the execution of the server process. the execution of a statement.
In other words, you can run a statement and have it pause half way through, before continuing on its merry way.
The three functions are:
These are all very similar, but they work in slightly different ways.
Below are examples of each one.
Continue readingHow IIF() Works in SQL Server
In SQL Server, the IIF() function (not to be confused with the IF statement) is a conditional function that returns the second or third argument based on the evaluation of the first argument.
It’s a shorthand way for writing a CASE expression. It’s logically equivalent to CASE WHEN X THEN Y ELSE Z END assuming IIF(X, Y, Z).
IIF() is an abbreviation for Immediate IF.
How the IF Statement Works in SQL Server
Most (probably all) programming languages include some form of the IF statement that allows programmers to write conditional code. That is, code that will execute only if a certain condition is true.
It’s a very simple concept. Basically it goes like this:
“If this, do that.”
Most languages simply call it IF, but some have their own twist on the name (for example, in ColdFusion/CFML, it’s called CFIF).
In any case, they essentially do the same thing.
In SQL Server (or more precisely, its programming language T-SQL) it’s called IF.
Format the Month in Roman Numerals in PostgreSQL
In PostgreSQL, you can use the to_char() function to return dates in various formats.
One of the things you can do with this function is return the month portion of a date in roman numerals.
Continue readingHow to Set the Interval Output Format in PostgreSQL
When working with the interval data type in PostgreSQL, you can change the way the interval output is formatted.
You have a choice of four formats that your intervals can be output in.
Continue readingCalculate the Age in Years in PostgreSQL
Postgres has the age() function that returns the age in years, months, and days based on two dates.
This works fine unless you only want to return the age in years.
For example, you simply want to return a person’s age based on their birthday. You want something like 32 instead of 32 years 4 mons 67 days, which is what age() is likely to return.
Fortunately there’s an easy way to do this in PostgreSQL.
Continue readingGet the Week Number from a Date in PostgreSQL
In PostgreSQL you can use the extract() function to get the week number from a date.
You can also use the date_part() function to do the same thing.
Get the Short Day Name in PostgreSQL
In PostgreSQL, you can use the to_char() function to get the short day name from a date.
By “short day name” I mean the abbreviated day name, for example Fri, Mon, etc.
The to_char() function accepts two parameters; the date and the format string with which to format that date.