Cross-platform SQL date difference function or query

Question

I have a table that has a datetime column and I need to retrieve records with a date within the previous hour, day, week, month etc.

One constraint is that I need to support Oracle, MySQL, DB2 and SQL SERVER. Can I achieve this with one SQL query only?

Answer 1

Since they all can handle datetimes differently, I'm going to guess you can't. You might be able to separate out the platform-specific stuff and swap it when the platform changes.

If you tell us more about this problem, we might be able to suggest something in more detail.

Answer 2

The ANSI SQL funtion would be datediff(). But I doubt all (especially older) SQL dialects support it.

Answer 3

This sounds like a good reason to start up on a multi-tiered RDBMS-based-approach to retrieving data.

Answer 4

User-defined functions. Write your own DATEDIFF-equivalent for each RDBMS and abstract away the differences.