Get Year of Previous Month (For Jan 1st)

2
votes

I'm currently building a YTD report in SSRS. I'm looking to edit the default "FROM" date in a calendar selection.

I'm looking to retrieve January 1st of the previous months year. For example:

(If it's Feb 16th, 2016 .. the result should be 1/1/2016

If it's Jan 10th, 2016 .. the result should be 1/1/2015)

I built this to retrieve the current year for jan 1st, but it causes issues if we're in January because I need it to retrieve the year of the previous month (in that case it would be 2015, not 2016).

Thanks!

3
sqlsql-serverreporting-services
Maybe something like this? Year(DateAdd(m,-1,Parameter!myParameter.Value)) – mxix
Please google and learn about Expressions in SSRS. You can write an expression for the default value of your parameter. – Tab Alleman

3 Answers

1
votes

Try this, it should work

=DateAdd(DateInterval.Month,-1,DateSerial(Year(Today), Month(Today), 1))

UPDATE:

Based on your comment I've created this expression. It is not tested but should work.

=IIF(Today.Month>1,
DateAdd(DateInterval.Month,-1,DateSerial(Year(Today), Month(Today), 1)),
DateAdd(DateInterval.Year,-1,DateSerial(Year(Today), Month(Today), 1))
)

Let me know if this helps.

0
votes
select cast(cast(year(dateadd(mm, -1,getdate())) as varchar)+'-01-01' as date)

replace getdate() with which ever field you're basing this calculation on.

for testing:

select cast(cast(year(dateadd(mm, -1,'2015-01-22')) as varchar)+'-01-01' as date)
select cast(cast(year(dateadd(mm, -1,'2016-02-01')) as varchar)+'-01-01' as date)
select cast(cast(year(dateadd(mm, -1,'2015-12-12')) as varchar)+'-01-01' as date)
0
votes

We want to use Date Serial which has the forma =DateSerial(YYYY,MM,DD) The Month is always January The day is always the first If the month is January, it's last year. Otherwise, it's this year. So, assuming we have an SSRS report with a parameter called Date , we can create a new field for your January date as follows:

=iif(Month(Parameters!Date.Value)=1, 
    dateserial(Year(Parameters!Date.Value)-1,1,1),
    dateserial(Year(Parameters!Date.Value),1,1))

if you want to do this in the query with T-SQL (version 2012) or later:

case when month(@DATE) = 1 
    then DATEFROMPARTS(YEAR(@DATE)-1,1,1)
    else DATEFROMPARTS(YEAR(@DATE),1,1) 
 end

OR, in earlier versions

  CASE WHEN MONTH(@DATE) = 1 
        THEN CAST(CAST((YEAR(@DATE)-1)*10000 + 101 AS CHAR(8)) AS DATE) 
        ELSE CAST(CAST((YEAR(@DATE)*10000+ 101) AS CHAR(8)) AS DATE) 
    END