CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008bidsssrs-expression
JsonStatham
JsonStatham

Reputation: 10374

SSRS BIDS expression language Month Part from Current date

In BIDS I need an expression to take the YEAR part of a date which is minus 1 month of the current date.

For example Today's date is 20/04/2012 the desired expression would return:

2012

If todays date was 05/01/2012 the desured expression would return:

2011

Upvotes: 2

Views: 910

Answers (3)

Diego
Diego

Reputation: 36156

so the rule is:

  • If it's January -> return last year
  • else -> return this year

Right?

=IIf(Datepart("m", Fields!date.Value)=1,Datepart("YYYY", Fields!date.Value)-1,Datepart("YYYY", Fields!date.Value))

Upvotes: 1

Andrew McLintock
Andrew McLintock

Reputation: 142

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION YearRollOver 
(

    @dDateIn DATETIME
)
RETURNS DATETIME

AS

BEGIN

    declare @dDATEOut  DATETIME


    SELECT @dDATEOut = CAST(CAST(YEAR(@dDateIn)+1 AS varchar)+'/'+CAST(MONTH(@dDateIn) as varchar)+'/'+cast(DAY(@dDateIn) as varchar)  AS DATETIME)

    RETURN @dDATEOut

END
GO

Try this function. Just pass it the date to increment and it will up your YEAR by 1.

Remember to close the thread if you are happy.

Cheers

Mac

Upvotes: 1

Andrew McLintock
Andrew McLintock

Reputation: 142

If its something you are gong to do a lot of I would create a function to do this which would extract the year, month , and day - each to its own var. Increment the Year, rebuild the date as a string and CAST the string as DATE. If you need a hand let me know.

Mac

Upvotes: 0

Related Questions