# Calculated date

• ### 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:

Thanks,

Eshwar.

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

• ```-- 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 Thursday, March 23, 2017 1:52 AM
• Marked as answer by 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
• ```-- 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 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)
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 Thursday, March 23, 2017 1:52 AM
• Marked as answer by 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