calulate start of financial year in Sql Reporting Services

Hi
We use Microsoft Sql Reporting Serives 2005.  Some of our reports require a calculation to be made that works out the difference in days between the start of the financial year (i.e. 1st of July) to a date stored in in each record.  Can anyone think of an expression that calculates the date of the start of the current financial year.  ie. this year the date would be 1/7/2009 and next year it would be 1/7/2010 etc
I don't want to have to manually change the date everytime the year changes.

Many Thanks
Scott
LVL 1
protocol1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
I haven't worked with Reporting Services, but would this need to be a SQL expression?

~bp
0
Chris LuttrellSenior Database ArchitectCommented:
I think you want an SSRS Expression, don't you?  I am trying to work one out.
0
protocol1Author Commented:
Yes I'm after an SSRS Expression as the caculation is being done by a report filter rather than a SQL query

Thanks
Scott
0
Chris LuttrellSenior Database ArchitectCommented:
Try this, it looks like it works for me, it returns the date equivalent to July 1, 2009 if I run it on my machine right now.
One note, I have "7/1" because my machine is in US format, you may need to switch that if yours is some other default format.

=CDate("7/1/"&Cstr(IIF(Month(Today)<7,Year(Today)-1,Year(Today))))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
protocol1Author Commented:
Excellent, that's exactly what I needed.

Thanks
Scott
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.