locked
Calculated date RRS feed

  • Question

  • Hi Team,

    I have requirement to add months to current date and adjust to value in the column dateofmonth taking into consideration invalid date after adjustments.

    Below is more details:

    Appreciate your help.

    Thanks,

    Eshwar.


    • Edited by Eswararao C Wednesday, March 22, 2017 4:34 AM
    Wednesday, March 22, 2017 4:33 AM

Answers

  • -- Can you try this again
    DECLARE @tbl AS TABLE( [currentDate] DATETIME ,[DateOfMonth] INT ,MonthsAddition INT  )
    
    INSERT INTO @tbl ( currentDate ,DateOfMonth , MonthsAddition  ) VALUES ('2017-3-22',22,5) ,  ('2017-3-22',25,5),  ('2017-3-22',10,5),  ('2017-3-22',30,11),  ('2017-3-22',31,3);
    -- Code from here 
    
    ;WITH t AS 
    (
       SELECT * ,  DATEADD(MONTH , MonthsAddition , currentDate )  AS CalcDate FROM @tbl
    )
    select  currentDate ,    DateOfMonth , MonthsAddition ,
    	Case 
    	when  DateOfMonth  < DATEPART(DD,currentDate) then DateAdd(month, 1 ,CalculatedDate) 
    	ELSE CalculatedDate 
    	END As CalculatedDate
     from 
    (
      SELECT currentDate ,    DateOfMonth , MonthsAddition  , 
      CASE
        WHEN  ISDATE( DATENAME( YEAR , CalcDate) + '-' + DATENAME( MONTH , CalcDate)  + '-' +  CAST(  DateOfMonth AS VARCHAR(2))   )=1
        THEN DATEADD(dd, 0, DATENAME( YEAR , CalcDate) + '-' + DATENAME( MONTH , CalcDate)  + '-' +  CAST(  DateOfMonth AS VARCHAR(2)))
        ELSE  DATEADD( MONTH , 1 , DATENAME( YEAR , CalcDate) + '-' + DATENAME( MONTH , CalcDate)  + '-' +  '01')-1
        END AS  CalculatedDate
      FROM t 
      ) b

    • Proposed as answer by TWoW2017 Thursday, March 23, 2017 1:52 AM
    • Marked as answer by Eswararao C Thursday, March 23, 2017 12:13 PM
    Wednesday, March 22, 2017 5:11 PM

All replies

  • Please post sample data + desired result. Always state what version you are using.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 22, 2017 6:20 AM
    Answerer
  • -- Can you try this 
    DECLARE @tbl AS TABLE( [currentDate] DATETIME ,[DateOfMonth] INT ,MonthsAddition INT  )
    
    INSERT INTO @tbl ( currentDate ,DateOfMonth , MonthsAddition  ) VALUES ('2017-3-22',22,5) ,  ('2017-3-22',25,5),  ('2017-3-22',10,5),  ('2017-3-22',30,11),  ('2017-3-22',31,3);
    -- Code from here 
    
    ;WITH t AS 
    (
       SELECT * ,  DATEADD(MONTH , MonthsAddition , currentDate )  AS CalcDate FROM @tbl
    )
    SELECT currentDate ,    DateOfMonth , MonthsAddition  , 
     CASE
     WHEN  ISDATE( DATENAME( YEAR , CalcDate) + '-' + DATENAME( MONTH , CalcDate)  + '-' +  CAST(  DateOfMonth AS VARCHAR(2))   )=1
     THEN DATEADD(dd, 0, DATENAME( YEAR , CalcDate) + '-' + DATENAME( MONTH , CalcDate)  + '-' +  CAST(  DateOfMonth AS VARCHAR(2)))
     ELSE  DATEADD( MONTH , 1 , DATENAME( YEAR , CalcDate) + '-' + DATENAME( MONTH , CalcDate)  + '-' +  '01')-1
     END AS  CalculatedDate
      FROM t 
    


    • Edited by msbi_Dev Wednesday, March 22, 2017 6:27 AM
    Wednesday, March 22, 2017 6:26 AM
  • Thanks Rajiv,

    Almost works except for third scenario where it should go to next month's DateOfMonth < Day of CurrentDate.

    Regards,

    Eshwar.


    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

    Wednesday, March 22, 2017 11:15 AM
  •  select *, case when dateofmonth<=day(currentDate) 
    then Dateadd(day,dateofmonth,eomonth(dateadd(month,MonthsAddition,currentDate),-1) ) 
    else eomonth(dateadd(month,MonthsAddition,currentDate) ) end 
    from yourtable
    

    Wednesday, March 22, 2017 1:24 PM
  • -- Can you try this again
    DECLARE @tbl AS TABLE( [currentDate] DATETIME ,[DateOfMonth] INT ,MonthsAddition INT  )
    
    INSERT INTO @tbl ( currentDate ,DateOfMonth , MonthsAddition  ) VALUES ('2017-3-22',22,5) ,  ('2017-3-22',25,5),  ('2017-3-22',10,5),  ('2017-3-22',30,11),  ('2017-3-22',31,3);
    -- Code from here 
    
    ;WITH t AS 
    (
       SELECT * ,  DATEADD(MONTH , MonthsAddition , currentDate )  AS CalcDate FROM @tbl
    )
    select  currentDate ,    DateOfMonth , MonthsAddition ,
    	Case 
    	when  DateOfMonth  < DATEPART(DD,currentDate) then DateAdd(month, 1 ,CalculatedDate) 
    	ELSE CalculatedDate 
    	END As CalculatedDate
     from 
    (
      SELECT currentDate ,    DateOfMonth , MonthsAddition  , 
      CASE
        WHEN  ISDATE( DATENAME( YEAR , CalcDate) + '-' + DATENAME( MONTH , CalcDate)  + '-' +  CAST(  DateOfMonth AS VARCHAR(2))   )=1
        THEN DATEADD(dd, 0, DATENAME( YEAR , CalcDate) + '-' + DATENAME( MONTH , CalcDate)  + '-' +  CAST(  DateOfMonth AS VARCHAR(2)))
        ELSE  DATEADD( MONTH , 1 , DATENAME( YEAR , CalcDate) + '-' + DATENAME( MONTH , CalcDate)  + '-' +  '01')-1
        END AS  CalculatedDate
      FROM t 
      ) b

    • Proposed as answer by TWoW2017 Thursday, March 23, 2017 1:52 AM
    • Marked as answer by Eswararao C Thursday, March 23, 2017 12:13 PM
    Wednesday, March 22, 2017 5:11 PM
  • Thanks Rajiv,

    Almost works except for third scenario where it should go to next month's DateOfMonth < Day of CurrentDate.

    Regards,

    Eshwar.


    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

    Can you please check my latest post
    Thursday, March 23, 2017 12:54 AM