establishing a year back - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

establishing a year back

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-23 : 09:27:06

I have this in a Stored procedure that takes the passed month and arrives at 1,2, and 3 months prior. I would like to now also get the same months prior but a year prior as well for the report to compare month/current year vs. month/prior year. These new 4 wouldn't be passed parameters though.

SET @STARTDATE1 = DateAdd (m , -1 , @EndDate +1)

SET @STARTDATE2 = DateAdd (m , -2 , @EndDate +1 )

SET @STARTDATE3 = DateAdd (m , -3 , @EndDate +1 )

SET @STARTDATE4 = DateAdd (m , -4 , @EndDate +1 )

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-23 : 09:31:16

use

dateadd (year, -1, yourdate)

KH
[spoiler]Time is always against us[/spoiler]

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-23 : 09:32:20

Hi

SELECT DATEADD(YEAR,-1,GETDATE())

-------------------------
R..

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-23 : 09:47:39

THank you, also I need to show on the report, it should show
for the column headings,

Jan 08 Jan 09 Feb 08 Feb 09 etc.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-23 : 10:05:12

quote:
Originally posted by Adam West

THank you, also I need to show on the report, it should show
for the column headings,

Jan 08 Jan 09 Feb 08 Feb 09 etc.

Do the formatting of the date in the report.

KH
[spoiler]Time is always against us[/spoiler]

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-23 : 10:12:04

I am not sure how you would do this in SSRS. In Crystal Reports, you just told it, give me that date as Month Year

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-23 : 11:42:20

I found a possible answer at another forum but it doesn't put the words of the month only does 04/09

=Format(Parameters!ENDDATE.Value, “MMM-yyy”)

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-23 : 12:01:42

That one works but how do you get it to format for one year ago?

=Format(Parameters!ENDDATE.Value, “MMM-yy”)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 04:26:57

quote:
Originally posted by Adam West

That one works but how do you get it to format for one year ago?

=Format(Parameters!ENDDATE.Value, “MMM-yy”)

You can use dateadd function.

=Format(DATEADD(year,-1,Parameters!ENDDATE.Value), “MMM-yy”)

I am not sure if the systax is valid in SSRS but it should be something like that

Madhivanan

Failing to plan is Planning to fail

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-24 : 09:31:31

=Format(DATEADD(year,-1,Parameters!ENDDATE.Value), “MMM-yy”)
there is an error here:
BC30455 Argument not specified for parameter 'DateValue' of 'Public Function Year(DateValue As Date) As Integer'.
Build complete -- 1 errors, 0 warnings

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 11:02:55

Is this?

=Format(DATEADD("YEAR",-1,Parameters!ENDDATE.Value), “MMM-yy”)

Madhivanan

Failing to plan is Planning to fail

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-24 : 11:25:05

Madhivana, no that one shows as #error as well

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-25 : 03:08:57

http://msdn.microsoft.com/en-us/library/aa337194(SQL.90).aspx

=Format(DATEADD("Y",-1,Parameters!ENDDATE.Value), “MMM-yy”)
or

=Format(DATEADD("YY",-1,Parameters!ENDDATE.Value), “MMM-yy”)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources