none
Add or subtract years RRS feed

  • Question

  • Hello,

    can anybody give me an idea how to find same date 2 years ago from 2017-05-30, get this date 2015-06-01

    thanks a lot

    Monday, September 9, 2019 6:43 PM

All replies

  • DECLARE @d date = '2017-05-30';
    SELECT @d, DATEADD(YEAR, -2, DATEADD(MONTH, DATEDIFF(MONTH, -1, @d), 0));

    A Fan of SSIS, SSRS and SSAS

    Monday, September 9, 2019 7:03 PM
  • Hi,

    You can also use like below:

    DECLARE @d date = '2017-05-25';
    SELECT @d,DATEADD(YY,-2,DATEADD(d, 1, EOMONTH(@d))); --2015-06-01

    Note: Suggested some of the many ways that are easy to understand and implement.
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Tuesday, September 10, 2019 4:01 AM
  • Hi  Jasmin,

     

    I'm not sure how you get the date' 2015-06-01', cus

    1.  If you count every year as 360 days, then you can try:

    declare @date date= '2017-05-30'
    select dateadd(day,-720,@date)
    /*
    ----------
    2015-06-10
    */

    or

    declare @date date= '2017-05-30'
    select dateadd(year,-2,@date)
    /*
    ----------
    2015-05-30
    */
    

    (2) if you count two years actual days, then it should be 365+ 366 = 731 days. You can try this one :

    declare @date date= '2017-05-30'
    DECLARE @year1 AS INT= datepart(yy,@date)
    select dateadd(day,-[TOTAL NO OF DAYS],@date) from (
    --/count the total days of two years 
    Select DATEPART(dy,DATEFROMPARTS(@Year1-2,12,31))+DATEPART(dy,DATEFROMPARTS(@Year1-1,12,31))
    AS [TOTAL NO OF DAYS])a
    /*
    ----------
    2015-05-30
    */
    

    Sabrina



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, September 10, 2019 4:42 AM
  • Hi

    The below couple of approaches also work as you expected. 

    --Approach-1
    SELECT DATEADD(Month,DATEDIFF(Month,'1900-01-01','2017-05-30')-23,'1900-01-01')
    
    --Approach-2
    SELECT DATEADD(Day,2,DATEADD(year,-2,'2017-05-30'))
    

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, September 10, 2019 4:44 AM
  • As per your problem statement, below is the simplest solution

    Query: SELECT DATEADD(Day,2,DATEADD(year,-2,'2017-05-30'))

    Explanation: We are first adding -2 years from the current date, which gives date as '2015-05-30' (DATEADD(year,-2,'2017-05-30')) and then we can add 2 days which will give output as '2015-06-01'

    However if we want to check the exact date 2 years back I think we should follow the below solution:

    Query: SELECT DATEADD(Day,-731,'2017-05-30')

    Explanation: We have 2 years to subtract (1 leap year and 1 non-leap Year). For leap years we need to consider 366 days and for non-leap years 365 days will be counted. So total days we need to subtract 731 days in order reach to the desired date: '2015-05-30' 

    • Proposed as answer by D Maestro Tuesday, September 10, 2019 5:59 AM
    Tuesday, September 10, 2019 5:53 AM
  • Hi

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue, please  mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue. 

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, September 12, 2019 4:42 AM