1yr,3yr and 5yr + Date calculation

Question

Post reply

1yr,3yr and 5yr + Date calculation

karthik M

One Orange Chip

Points: 29139
More actions
September 19, 2008 at 2:19 am

#71096

All,
I have to find the startdate and enddate for 1yr,3yr and 5yr.
i.e if user choose any date then i have to take the previous month's last date first then need to calculate backward to calculate the one year date.
say for example,
user has choosen 19/sep/2008.
Step 1: Previous month's last date i.e Aug/31/2008
Step 2: we need to traverse backward from Aug/31/2008 to 12 month ( i.e 1 yr)
Step 3: Result would be Sep/01/2007.
Step 4: we need to traverse backward from Aug/31/2008 to 36 month ( i.e 3 yr)
Step 5: Result would be Sep/01/2005
Step 6: we need to traverse backward from Aug/31/2008 to 36 month ( i.e 5 yr)
Step 7: Result would be Sep/01/2003
Inputs are welcome !
karthik

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

[font="Courier New"]DECLARE @MyDate DATETIME

SET @MyDate = CAST('2008-09-19' AS DATETIME) -- makes a date without a time component

SET @MyDate = DATEADD(dd,DATEDIFF(dd, 0, GETDATE()), 0) -- chop the time component off today

SELECT @MyDate AS Today,

DATEADD(dd,-1*DATEPART(dd, @MyDate), @MyDate) AS [Last date of prev. month],

DATEADD(mm, -12, DATEADD(dd,-1*DATEPART(dd, @MyDate)+1, @MyDate)),

DATEADD(mm, -36, DATEADD(dd,-1*DATEPART(dd, @MyDate)+1, @MyDate)),

DATEADD(mm, -60, DATEADD(dd,-1*DATEPART(dd, @MyDate)+1, @MyDate))

[/font]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 2

The following function should help you out:

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fnDateCountBack' AND xtype IN ('FN', 'IF', 'TF'))

DROP FUNCTION fnDateCountBack

GO

CREATE FUNCTION fnDateCountBack(

@inputDate DATETIME

)

RETURNS @table TABLE (yr SMALLINT, outputDate DATETIME)

AS

BEGIN

DECLARE @initialCutOff DATETIME

SELECT @initialCutOff = DATEADD(DD, -1 * (DATEPART(DD, @inputDate) -1), @inputDate)

INSERT INTO @table SELECT -1, DATEADD(YY, -1, @initialCutOff)

INSERT INTO @table SELECT -3, DATEADD(YY, -3, @initialCutOff)

INSERT INTO @table SELECT -5, DATEADD(YY, -5, @initialCutOff)

RETURN

END

GO

SELECT * FROM dbo.fnDateCountBack('2008-09-19')

🙂

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

karthik M One Orange Chip Points: 29139 More actions · Answer 3

Chris and Adrian,

Thanks for your help !

Meantime i found one way to do it. But i have used more CONVERT function. I think mine will leads to performance issue. I would like to bring my code to your people notice to know the performance of my code.

Declare @One_Yr_End datetime,

@One_Yr_Start datetime,

@Three_Yr_Start datetime,

@Five_Yr_Start datetime

Select @One_Yr_End = convert(datetime,dateadd(dd,-1,convert(datetime,convert(varchar(4),datepart(mm,getdate()))+'/'+'01'+'/'+convert(varchar(4),datepart(yy,getdate())))))

Select @One_Yr_End

Select @One_Yr_Start = convert(datetime,dateadd(dd,1,dateadd(yy,-1,@One_Yr_End)))

Select @One_Yr_Start

Select @Three_Yr_Start = convert(datetime,dateadd(dd,1,dateadd(yy,-3,@One_Yr_End)))

Select @Three_Yr_Start

Select @Five_Yr_Start = convert(datetime,dateadd(dd,1,dateadd(yy,-5,@One_Yr_End)))

Select @Five_Yr_Start

karthik

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 4

Is there a reason you can't test it yourself and post the results?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)