1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Last Day of a the year 2 or 3 years ago

Discussion in 'SQL PL/SQL' started by BrianBattles, Jan 19, 2015.

  1. BrianBattles

    BrianBattles Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Central CT USA
    Is there a simple way to return the last day of the year 2 or 3 years ago?

    I have a huge payroll table, and I'd like to only return records for the past 2 years (or sometimes 3). I don't want to specify any dates in my query, I just want it to get anything after date X where X is December 31, CURRENTYEAR-2 or where X is December 31, CURRENTYEAR-3

    There appear to be a lot of ways to do this, I want to use the simplest. Every time I try to write this, my code gets longer and more convoluted.

    My latest try was this, but I don't like hard coding a number of months:

    Code (Text):
    SELECT LAST_DAY(ADD_MONTHS(LAST_DAY(TRUNC(SysDate,'YEAR')), -13)) "Two Years Ago First Day" FROM Dual
    Thanx!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Even using -2 or -3 hard codes a reference point thus you're not going to get away from that act. To me it appears you have the most 'efficient' and 'elegant' way to get these dates.
     
  3. BrianBattles

    BrianBattles Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Central CT USA
    I suppose so; I don't mind having the number of years hard coded, I just don't want to have to change the code depending on what month or day it is. There are 80 million plus records in one of these tables, and I am running this in MS Access using a pass-through query, and in the query I'm running a couple of subqueries to help limit the number of records returned, and to speed up processing. Most of the reports are only looking for data in the past year or two.

    Thanks for the help, David!
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Brian,

    As David mentioned, you can't escape some level of "hard-coding"; another option you may use could be:

    Code (SQL):
    SELECT
    TRUNC(SYSDATE - INTERVAL '1' YEAR, 'YY')-1 "Last Day of Year 2 Years Ago",
    TRUNC(SYSDATE - INTERVAL '2' YEAR, 'YY')-1 "Last Day of Year 3 Years Ago"
    FROM Dual;
     
     
    BrianBattles likes this.
  5. BrianBattles

    BrianBattles Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Central CT USA
    Handy Rajen...and simpler...thanks!