I need to get the difference (in months) between 2 dates through SQL query.

Kindly suggest.

Hi,

You can use this query :

select distinct (month ('6/2/1994') - month ('5/2/1994')) UNITS month from customer

Modify the same as per ur needs.

Hope it helps.

Nitin

I have been through this exercise this week with several tables and found the following to be much easier than unloading to files. The benefit here is you don't drop the original table until you are sure the new table has all the data but it does mean you need a fair bit of spare dbspace to have both tables existent at the same time for a period of time:

1. Create the new table with the correct extent size

2. Create a schema of the database as it stands
DBSCHEMA -d dbname -ss dbname.sql

3. Read dbname.sql and make a note of any view or trigger that refers to the table in question. You will need these to recreate the views or triggers as these are invalidated when you drop the table.

4. Copy the data from the old table:
INSERT INTO newtable SELECT * FROM table;

5. Drop any indexes on the old table:
drop index blah;

6. Check both tables have the same number of rows
select count(*) from table;
select count(*) from newtable;

7. Run oncheck -pe and look at new extents ensuring the new table is not fragmented

8. Rename old table
RENAME table TO oldtable;

9. Rename new table
RENAME newtable TO table;

10. Create index(es) on the new table

11. Grant permissions to the new tble e.g.
GRANT SELECT ON "informix".table TO "public" AS "informix";

12. Finally drop the old table if you are abosulutely sure we don't need it
DROP TABLE oldtable;

13. Recreate any views or triggers

Wrong thread sorry!

Be advised - this method may not do what you need it to do. The month() function only extracts the numeric (1-12) month. There is no consideration given to the true difference between two dates. For example month ('6/2/1994') - month ('6/2/1995') is 0, not 12.

I am sorry, I missed that. I think this can be achieved by using a procedure only.