Leap Year calculation in SSRS | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Leap Year calculation in SSRS

Hi,
I am using SSRS interface to create and schedule a report.
The report parameters are as below…
Start Date: 01/03/2008 (dd/mm/yyyy)
End Date: 12/12/2009 (dd/mm/yyyy)
Schedule Details: Monthly (Feb) on 29th
Which means the report should not execute as 29th Feb will not appear on 2009. This date will come only in 2012 which is a leap year.
What SSRS does is, it creates a report and schedules it on 29th March 2009 (29/03/2009)
Which is wrong.
We have tried this issue several times with different date combination and the result was same. Did any one come accross this type if issue? If yes please let me know the solution. Is this a SSRS BUG??
With regards
Anirban

What if you change the end date to 2012? Do you still get the same?
Another question: Why dont you just schedule it to run on 29 Feb 2012, as you know this will be the only time it needs to run?

It would need to run this year too.[;)]
I guess it needs to run once a year, the day before March 1. Perhaps 00:00 AM on March 1 is close enough?

True [:D]

Hello,
I Need ur help will u plz tell me how can we see all locks from server and session name ,session id with t-sql statements
thaxxx .
if u solve this
regards
jagpal singh

Jagpal – you’re on the wrong thread here.

In case if you want to know last day of Feb, then
select dateadd(month,2,dateadd(year,datediff(year,0,getdate()),0))-1

You change the report schdule to be run in last date of the month

Anirban –
Yes, this is a bug in SSRS. I filed a bug internally to address it in SQL Server 2008. Essentially, this is an artifact of some logic we have in RS that is required to ensure the correct schedule is created in SQL Agent. Unfortunately, it does not account for leap years.
What this affects:
It affects recurring schedules that run every February 29th. This is somewhat uncommon.
What it does not affect:
You can still create schedules that run on February 29th, if they are not recurring. So you can create a schedule with recurrence "once" that runs today.
My recommendation for issues like this is to file a bug on http://connect.microsoft.com if you continue to see issues with dates.
Thanks,
-Lukasz

As a work around create a subscription to run on first of every month and use (currentdate – 1) as your report parameter.

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |