Subtract a date by X number of Months

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Subtract a date by X number of Months

Author

Topic

stuaz
Starting Member

16 Posts

Posted - 2014-07-23 : 12:05:05

Hi,

I have three fields of data -

STARTDATE - (datetime)
ENDDATE - (datetime)
FREQUENCY - numeric

How could change the STARTDATE to be the ENDATE minus the months from whatever the value is in FREQUNCEY.

Example:

StartDate - 01/01/2014
End Date - 01/06/2014
Freq - 3

So Start Date needs to be 01/06/14 minus 3 months.

01/03/14.

It also needs to take into account different years... so the end date could be 01/01/2014 and the Frequency 6 so the Start Date should be changed to 01/06/13.

Even though they are datetime fields the time is always 00:00:00.000

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-23 : 12:42:53

DECLARE @STARTDATE datetime = '1/1/2014',
@ENDDATE datetime = '1/6/2014',
@FREQUENCY numeric =3

SELECT @STARTDATE = DATEADD(MM,-1*@FREQUENCY,@ENDDATE)
SELECT @STARTDATE

-- U.S date format

stuaz
Starting Member

16 Posts

Posted - 2014-07-24 : 09:29:27

Thanks, now I just have to work out how to fit that into my query....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources