Search code examples
sqlingresdate-math

DATE_ADD functionality in Ingres

Many (most? nearly all?) SQL dialects have a way to perform this type of function: date_add( MyDate, MyInterval )

I've been through all of the docs, but I cannot find this functionality in Ingres. My immediate goal is to get "MyDate plus 3 months". Does anyone know if there is a simple way to do this that I'm missing?

Note: I realize that it's possible to achieve this with existing SQL. But it will involve:

  • extract the month from my date
  • add 3 to this number
  • extract the day and year from my date
  • use the new day, month, year to create a new date
  • But I also need to test to see if I cross a year boundary, so there will be a CASE statement as well

That's an awful lot of SQL for something that's so simple in Oracle, MySQL, PostgreSQL, SQL Server, and all the others I can think of. It makes me hopeful that I'm somehow missing a much simpler alternative.

Solution

  • I don't have a link to hand. Search your documentation for 'date arithmetic' and/or the 'interval' datatype.

    Here's an example, which is not dissimilar to your english statement of what you want:

    DATE('23-oct-09') + '3 months'