Set start date to current Fiscal Year

I have a report where I am hardcoding the start date and I'd like to make it dynamically set the start date.

If today is equal to or greater than May 1 then set the start date to May 1st of this year.
If today is earlier than May 1 then set the start date to May 1st of last year.

I've already got a where clause of (Wdate > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) and I'm just having a brain-freeze on how to incorporate it in this where statement.  I think I take the '2013-05-01 00:00:00' and replace it with an if statement but I'm coming up blank when I try to figure this out.  I could also create a parameter but there isn't easier on the brain cells right now.

If anyone can help out I'd be eternally grateful!!
HSI_guelphAsked:
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
something like...
SELECT CASE Month(Wdate)
    WHEN > 4 THEN CAST(CAST(YEAR(Wdate) as varchar(4)) + '-05-01' as date) 
    ELSE CAST(CAST(YEAR(Wdate) -1 as varchar(4)) + '-05-01' as date) END as StartDate

Open in new window

btw How ugly is your fiscal year?  Reason I ask is because I have an article out on how to create a calendar table, and Part II is likely going to include how to add rows for fiscal_year, fiscal_month, etc. using mostly the same logic.
0
Tony303Commented:
You could create a function,
Here is one we use for the financial year.... being July 1.

CREATE FUNCTION [dbo].[fnc_fYear](@AsOf DATETIME ) 
RETURNS INT 

AS 
	BEGIN          

		DECLARE @Answer INT -- You define what you want here (July being your changeover month)         

		IF ( MONTH(@AsOf) < 7 )                 
			SET @Answer = YEAR(@AsOf)          
		ELSE                 
			SET @Answer = YEAR(@AsOf) + 1           

		RETURN @Answer  

	END    

GO

Open in new window


To see the result

SELECT  [dbo].[fnc_fYear] (GETDATE())

Which will output 2014
0
PortletPaulfreelancerCommented:
Using a Calendar table does make a lot of sense, and such a table could be placed where you can access it whenever you need it. e.g. a 'utilities database'

If you just want a quick calculation, I prefer to work with date functions especially if doing many such calculations (as varchar conversions are slower)

this should arrive at May 1 in the correct year
dateadd(MONTH,4,dateadd(YEAR,datediff(YEAR,0, dateadd(MONTH,-4,getdate()) ),0))

it deducts 4 months from getdate()
gets the year from this
adds that year to zero (which is 1900-01-01 )
adds back 4 months

examples:
GIVEN_A_DATE               FYSTART_STR  FYSTART_DT
Jan 15 2013 12:00:00:000AM  2012-05-01  May, 01 2012 00:00:00+0000
Feb 14 2013 12:00:00:000AM  2012-05-01  May, 01 2012 00:00:00+0000
Mar 16 2013 12:00:00:000AM  2012-05-01  May, 01 2012 00:00:00+0000
Apr 15 2013 12:00:00:000AM  2012-05-01  May, 01 2012 00:00:00+0000

May 15 2013 12:00:00:000AM  2013-05-01  May, 01 2013 00:00:00+0000
Jun 14 2013 12:00:00:000AM  2013-05-01  May, 01 2013 00:00:00+0000
Jul 14 2013 12:00:00:000AM  2013-05-01  May, 01 2013 00:00:00+0000
Aug 13 2013 12:00:00:000AM  2013-05-01  May, 01 2013 00:00:00+0000
Sep 12 2013 12:00:00:000AM  2013-05-01  May, 01 2013 00:00:00+0000
Oct 12 2013 12:00:00:000AM  2013-05-01  May, 01 2013 00:00:00+0000
Nov 11 2013 12:00:00:000AM  2013-05-01  May, 01 2013 00:00:00+0000
Dec 11 2013 12:00:00:000AM  2013-05-01  May, 01 2013 00:00:00+0000
Jan 10 2014 12:00:00:000AM  2013-05-01  May, 01 2013 00:00:00+0000
Feb 9  2014 12:00:00:000AM  2013-05-01  May, 01 2013 00:00:00+0000
Mar 11 2014 12:00:00:000AM  2013-05-01  May, 01 2013 00:00:00+0000
Apr 10 2014 12:00:00:000AM  2013-05-01  May, 01 2013 00:00:00+0000

May 10 2014 12:00:00:000AM  2014-05-01  May, 01 2014 00:00:00+0000
Jun 9  2014 12:00:00:000AM  2014-05-01  May, 01 2014 00:00:00+0000
Jul 9  2014 12:00:00:000AM  2014-05-01  May, 01 2014 00:00:00+0000
Aug 8  2014 12:00:00:000AM  2014-05-01  May, 01 2014 00:00:00+0000
Sep 7  2014 12:00:00:000AM  2014-05-01  May, 01 2014 00:00:00+0000
Oct 7  2014 12:00:00:000AM  2014-05-01  May, 01 2014 00:00:00+0000
Nov 6  2014 12:00:00:000AM  2014-05-01  May, 01 2014 00:00:00+0000
Dec 6  2014 12:00:00:000AM  2014-05-01  May, 01 2014 00:00:00+0000

Open in new window

the query and data for that result:
SELECT
       convert(varchar,[a_date],9) AS given_a_date

     , convert(varchar(10),
              dateadd(MONTH,4,dateadd(YEAR,datediff(YEAR,0, dateadd(MONTH,-4,[a_date]) ),0))
         , 120)
       AS FYstart_str

     , dateadd(MONTH,4,dateadd(YEAR,datediff(YEAR,0, dateadd(MONTH,-4,[a_date]) ),0))
       AS FYstart_dt

FROM TestDates;

CREATE TABLE TestDates
	([a_date] datetime)
;
	
INSERT INTO TestDates
	([a_date])
VALUES
	('2013-01-15 00:00:00'),
	('2013-02-14 00:00:00'),
	('2013-03-16 00:00:00'),
	('2013-04-15 00:00:00'),
	('2013-05-15 00:00:00'),
	('2013-06-14 00:00:00'),
	('2013-07-14 00:00:00'),
	('2013-08-13 00:00:00'),
	('2013-09-12 00:00:00'),
	('2013-10-12 00:00:00'),
	('2013-11-11 00:00:00'),
	('2013-12-11 00:00:00'),
	('2014-01-10 00:00:00'),
	('2014-02-09 00:00:00'),
	('2014-03-11 00:00:00'),
	('2014-04-10 00:00:00'),
	('2014-05-10 00:00:00'),
	('2014-06-09 00:00:00'),
	('2014-07-09 00:00:00'),
	('2014-08-08 00:00:00'),
	('2014-09-07 00:00:00'),
	('2014-10-07 00:00:00'),
	('2014-11-06 00:00:00'),
	('2014-12-06 00:00:00')
;

--http://sqlfiddle.com/#!3/8ee62/1

Open in new window

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
HSI_guelphAuthor Commented:
We have a table for our Fiscal Year but it only holds the last 2 years!  I don't understand why that is.  But we lost 2 of the drives in our hard drive array and are currently repairing the sql database.  Something got corrupted and our SharePoint and Reporting Services are down as we try to fix this so I'll close for now and give these a try when we are back up and running.  Thank you very much for the replies!!
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.