Showing posts with label date functions. Show all posts
Showing posts with label date functions. Show all posts

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))