Getting the Year from a Date Column

So I was running our unit tests that we had created after moving to PostgreSQL, and I found a database specific error. I am so thankful for our unit tests now. Instead of testing the application by hand, all I had to do was click the mouse button. I can't stress enough how important tests are.

Anyways, what I had in my code was a specific call to a MySQL function called YEAR(). This apparently was not supported by PostgreSQL. After doing some searching, I found another SQL function called EXTRACT. This seems to be supported in several databases including MySQL, PostgreSQL, and Oracle(9i, 10g, 11g).

For my purposes of getting the year, I used:

EXTRACT(YEAR FROM date_column)

More information about the EXTRACT function can be found here.

Hopefully this helps anyone having database portability problems!


W