calculating dates

Question

calculating dates

timotech

SSCrazy

Points: 2698
More actions
March 14, 2011 at 7:43 am

#248186

Hi all i don't know what i'm doing wrong, i have this sql to calculate a particular date:
SELECT DATEADD(dd, - 365, @BeginDate) AS Expr1, @BeginDate AS Currentdate
it works actually for a @BeginDate = '1 jan 2011' and returns '1 jan 2010'
but i want to actually calculate exactly one year from this date, so i tried, '31 dec 2011' but its bringing an arithmetic overflow error converting data to datetime.
please what could be wrong.
Thanks
Timotech

Viewing 9 posts - 1 through 8 (of 8 total)

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

timotech (3/14/2011)
Hi all i don't know what i'm doing wrong, i have this sql to calculate a particular date:
SELECT DATEADD(dd, - 365, @BeginDate) AS Expr1, @BeginDate AS Currentdate
it works actually for a @BeginDate = '1 jan 2011' and returns '1 jan 2010'
but i want to actually calculate exactly one year from this date, so i tried, '31 dec 2011' but its bringing an arithmetic overflow error converting data to datetime.
please what could be wrong.

If you test statements below...

SELECT DATEADD(dd, - 365, '1 jan 2011') AS Expr1

SELECT DATEADD(dd, - 365, '31 dec 2011') AS Expr1

...both will return correct results therefore something is wrong with the way @BeginDate is being declared and/or populated.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 2

If you're looking for year you may want to do DATEADD(yy,-1,@begindate) to subtract a year. The day method may not do what you want if you're going through a leap year.

I'm trying to figure out if you're trying to subtract a year from @begindate or add a year. If you're trying to add a year just take the negative away, DATEADD(yy,1,@begindate).

As to the overflow error, if you post all related code, both the statement and how the variable is populated, we can help you better. Chances are the date format isn't one that's recognized by your server settings.

timotech SSCrazy Points: 2698 More actions · Answer 3

Guys, thanks for your contributions, actually u're all right, but what i noticed is that when i use query designer, it brings that error, but declaring @BeginDate as datetime in a new query window does not bring the error.

Thanks all.

timotech SSCrazy Points: 2698 More actions · Answer 4

Hi all, please how can i ensure that i get accurate dates for example, if i enter '28 feb 2011' for @BeginDate, and i count one year back, i want to have '1 mar 2010' as my one year value, please how can i achieve this, same for may be six months back, i want to have '1 sep 2010'.

Thanks

Timotech

WayneS SSC Guru Points: 95460 More actions · Answer 5

timotech (3/14/2011)
Hi all, please how can i ensure that i get accurate dates for example, if i enter '28 feb 2011' for @BeginDate, and i count one year back, i want to have '1 mar 2010' as my one year value, please how can i achieve this, same for may be six months back, i want to have '1 sep 2010'.
Thanks
Timotech

DECLARE @Date DATETIME;

SET @Date = '20110228';

-- Advance to start of next month:

-- Get the # of months between specified date and date "0" (01/01/1900).

-- Add these months, plus one, to date "0"

SET @Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date)+1, 0);

SELECT DATEADD(MONTH, -12, @Date), DATEADD(MONTH, -6, @Date)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Is this what you are looking for?

DECLARE @BeginDate DATE

DECLARE @W DATE

SET @BeginDate = '28 feb 2011'

--Included so as to see what is happening -remove in actual use

SET @W = DATEADD(dd,+ 1,DATEADD (mm , -12, @Begindate ))

----------------------------

SELECT DATENAME(dd,DATEADD(dd,+ 1,DATEADD(mm , -12, @Begindate )))+ ' '

+ DATENAME(mm,DATEADD(dd,+ 1,DATEADD (mm , -12, @Begindate ))) +' '

+ DATENAME(yy,DATEADD(dd,+ 1,DATEADD (mm , -12, @Begindate )))

Returns:

1 March 2010

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

timotech SSCrazy Points: 2698 More actions · Answer 7

Thanks Guys, you are all very correct, everything works very fine. Thannks

Ron McCullough SSC Guru Points: 63877 More actions · Answer 8

timotech (3/15/2011)
Thanks Guys, you are all very correct, everything works very fine. Thanks

Thank you, for letting all who answered know that you have been helped. It is appreciated.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]