Tuesday, November 24, 2009

T-SQL: set end date to first or fifteenth of the month

Here is a simple assignment: if the subscription date is from first to 15th of the month then the expiration date should be set to the 15th of the same month a number of years later; if the subscription date is from 16th to the end of the month then the expiration date should be set to the first of the following month a given number of years later.

I am sure there are many ways to do this but here is one:

DECLARE @sDate DATETIME -- subscription date
DECLARE @sYears tinyint -- number of years the subscription will last
SET @sDate = getdate()
SET @sYears = 1

IF datepart(dd, @sDate) <= 15

SELECT DATEADD(year, @sYears, @sDate) - datepart(dd, @sDate) + 15
ELSE
SELECT DATEADD(year, @sYears, DATEADD(mm, DATEDIFF(m,0,@sDate)+1,0))

1 comment:

Delphi said...

yes, i agree. Its a perfect blog to read. May i ask you your opinion about my blog? It will be great to hear your opinion.

http://greatdelphi.blogspot.com