A bit more info...when I execute the proc in SSMS it is returning that 2/29 perf. So it must be something in Infomaker?
Hello All,
I have a custom report that is not returning the perfs for 2/29/16 and I think it is because it is a leap year. I have found articles online that talk about creating a leap year function to test if it is a leap year. I've also read that maybe it is caused by the datetime data type. The custom report is pulling data from t_imp_perf_data where the perf date is a datetime type. Anyone have any ideas?
Gloria
How are the parameters to run the report set up? Is there a date range? Is the EOD box checked on the end-date parameter if so?
I found the needle in the haystack. TR_BATCH_PERIOD…the end date for February was set to 2/28. Mystery solved! Thanks anyway Andrea!
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Andrea Crain Sent: Thursday, July 09, 2015 7:15 PM To: Gloria Ormsby Subject: Re: [Tessitura Technical Forum] SQL and Leap Year
From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com> Sent: 7/9/2015 3:31:41 PM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Tricky! I have a procedure that automatically generates rows for each fiscal year (actually, a set of procedures to do all of our data; campaigns, appeals, seasons, etc.), and I very nearly forgot to handle this. I do it thusly (the procedure pulls the data from the primary year, modifies it, and inserts it into the tables for the upcoming year):
DATEADD(S, -1, DATEADD(M, 1, DATEADD(YY, 1, start_dt) ) ) as end_dt, --add a year and a month to last fyear's start date, then subtract a second --deals with leap year changes for February
Thanks Gawain.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Gawain Lavers Sent: Friday, July 10, 2015 1:10 PM To: Gloria Ormsby Subject: RE: [Tessitura Technical Forum] SQL and Leap Year
DATEADD(S, -1,
DATEADD(M, 1,
DATEADD(YY, 1, start_dt)
)
) as end_dt,--add a year and a month to last fyear's start date, then subtract a second
--deals with leap year changes for February
From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com> Sent: 7/10/2015 12:59:57 PM
Glad you got it sorted out!