Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Unanswered: diff. betwn. 2 dates in terms of months

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

    Kindly suggest.

  2. #2
    Join Date
    Nov 2004
    Posts
    143
    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

  3. #3
    Join Date
    Nov 2004
    Posts
    60

    Don't drop the table

    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

  4. #4
    Join Date
    Nov 2004
    Posts
    60

    Oops

    Wrong thread sorry!

  5. #5
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    Quote Originally Posted by nitin_math
    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
    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.
    Fred Prose

  6. #6
    Join Date
    Nov 2004
    Posts
    143
    I am sorry, I missed that. I think this can be achieved by using a procedure only.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •