I'm looking for a portable way to select all rows from a table where the value of a DATE column is within a certain month of a certain year. The problem trying to make such a select-query portable is that each database type has its own date format and each one has its own syntax to extract parts of this date.
For example, in MySQL a query to get all rows within a given month can look like this:
SELECT * FROM myTable WHERE MONTH(myDateColumn) = 12 AND YEAR(myDateColumn) = 2002
For PostgreSQL it's something like this (I believe):
SELECT * FROM myTable WHERE EXTRACT(MONTH FROM myTable) = 12 AND EXTRACT(YEAR FROM myTable) = 2002
Is there any way to rewrite this query so it will work on most SQL databases? If not, can somebody recommend me another way to store dates that will allow me to execute queries like the one above? I thought about storing the various parts of a date, i.e. year, month, day inside separate columns but that does not seem like a very elegant solution...