T-SQL Determine the next monthly occurance from a starting date

A sample script to determine the next monthly occurence from a start date


select
DateCreated,
dateadd(d,
 -- If the day of the end of the month > anniversary day then add the end of the month instead of anniversary
 case when datepart(d,datecreated)> datepart(d,dateadd(d,-1,dateadd(m,datediff(m,0,getdate())+1,0)))
 then datepart(d,dateadd(d,-1,dateadd(m,datediff(m,0,getdate())+1,0)))
 else datepart(d,datecreated)
 end
 -1,
 -- This month or next month if we are already past today
 dateadd(m,
 case when datepart(d,datecreated)
 then 1
 else 0
 end
 + datediff(m,0,getdate()),0)) CreatedAnniversary
from MyDB..MyTable
Advertisements

One thought on “T-SQL Determine the next monthly occurance from a starting date

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s