Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Location
    New Haven, CT USA
    Posts
    4

    Unanswered: Need help with CURRENT_DATE and INTERVAL

    I am faily new to SQL and I am trying to construct a query that returns all records with a specified date field is 1 year old or more.

    I have been rummaging through all the online SQL references, tutorials, etc for the last frew hours and all I have found is CURRENT_DATE which returns a DATE value for the current system date. I have also found refernences to the INTERVAL function, which I believe can specify date or time intervals.

    I think perhaps that these two applied together could perhaps provide an expression I could use in a where clause. but I cannot figure out the syntax, or even if I am on the right track.

    Basically what I want to accomplish is

    SELECT * FROM TABLE
    WHERE TABLE.DATEFIELD < CURRENT_DATE-(1 YEAR)

    But I do not know how to do this.

    Any help is greatly appreciated

    Thanks,

    Eric

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Did you try:
    Code:
    SELECT * FROM TABLE
    WHERE TABLE.DATEFIELD < CURRENT_DATE-365
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2004
    Location
    New Haven, CT USA
    Posts
    4
    I could try that. But how would it know I meant 365 days and not 365 minutes or hours for that matter.

    Actually, I was able to accomplish what I wanted with the following

    Code:
    SELECT * FROM TABLE
    WHERE TABLE.DATEFIELD <  '07/14/2003';
    This works for today. But what if I wanted to run this everyday without having to manually change the query? There has to be a way to automate this ...

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How would you know? Execute
    SELECT datefield, datefield - 365 last_year
    FROM table;
    and you'll see the result.

    Oracle interprets number constants in arithmetic date expressions as number of days. For example, SYSDATE + 1 is tomorrow. SYSDATE + (10/1440) is ten minutes from now.

    However, not all years have 365 days (Feb 29th, right?).

    There's a function named ADD_MONTHS in Oracle. Your example would then be
    SELECT * FROM table
    WHERE datefield < ADD_MONTHS(sysdate, -12);

    Is there something similar in SQL version you use?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you are using microsoft sql server

    SELECT * FROM TABLE
    WHERE TABLE.DATEFIELD < dateadd(yyyy,-1,getdate())

    if you are using microsoft access

    SELECT * FROM TABLE
    WHERE TABLE.DATEFIELD < dateadd("yyyy",-1,date())
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2004
    Location
    New Haven, CT USA
    Posts
    4
    Sorry guys, I am using Firebird 1.0.3. The only function mentioned that recently became available in Firebird 1.5 is ADD_MONTHS, which, of course, I cannot use until I upgrade. (Not a viable option at thiis time)

    However a special thanks to LKBrwn_DBA (and littlefoot with your follow up) since the syntax:

    CURRENT_DATE-365 did indeed work.

    Sometime the simplest solution is the best

    Thanks!

    Eric

Posting Permissions

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