r/SQL•
commitseppuku

Last day of the month from calculated column

I am trying to get a computed column setup in an MSSQL database which will move 6 months into the future from the date in another column and tell me what the last day of that month is.

This is to calculate a due date which is 6 months in the future from whatever date, rounded to the end of the month.

My current compute statement moves 6 months in the future, is it possible to have it also grab the last day of the month it lands in?

(dateadd(month,(6),[YEAREND_DATE]))

Posted Oct 3, 2014, 6:04 PM UTC

15
[deleted]•

Pinal Dave wrote a blog post on this.

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+6,0))
3
zerophaze•

I know SQL 2012 has EOMONTH function http://msdn.microsoft.com/en-us/library/hh213020.aspx.

2
commitseppukuOP•

I wish I could update, we are on 2008.

1
Xenius•

I think I understand what you want.

Assuming T-SQL:

SELECT @my_date = DATEADD(month,6,yearend_Date)

SELECT DATEADD(DAY, -1, DATEADD(MONTH, 1, CONVERT(VARCHAR, Month(@my_date )) + '/1/' + CONVERT(VARCHAR, YEAR(@my_date ))))

What this will do is convert the date you have to the first day of the month, then add one month, then subtract a day, leaving you with the last day of the month you started with.

1
commitseppukuOP•

This seems to make sense. Will that code all go into the computed field within the MSSQL Management Studio?

1