Calculating beginning of fiscal year based on current date

I have to calculate the beginning of the current fiscal year, July 1, based on the current date.

For 6/11/2014 the beginning of the Fiscal Year would be '2013-07-01'.

For 8/15/2015 the beginning of the Fiscal Year would be '2015-07-01'.

What is proper syntax to calculate this?

Thanks

Glen
GPSPOWAsked:
Who is Participating?
 
chaauCommented:
I think this will do:
convert(date, convert(varchar(4), case when datepart(month, yourdate) < 7 then datepart(year, yourdate)-1 else datepart(year, yourdate) end)+'0701') 

Open in new window

SQL Fiddle
0
 
HuaMin ChenSystem AnalystCommented:
Try this way
declare @dt datetime,
	@ret_dt datetime
select @dt=convert(datetime,'20140611',112)

if (substring(convert(varchar,@dt,112),5,4)>'0630')
	select @ret_dt=convert(datetime,substring(convert(varchar,@dt,112),1,4)+'0701',112)
else
	select @ret_dt=convert(datetime,substring(convert(varchar,dateadd(year,-1,@dt),112),1,4)+'0701',112)
print @ret_dt

declare @dt datetime,
	@ret_dt datetime
select @dt=convert(datetime,'20140711',112)

if (substring(convert(varchar,@dt,112),5,4)>'0630')
	select @ret_dt=convert(datetime,substring(convert(varchar,@dt,112),1,4)+'0701',112)
else
	select @ret_dt=convert(datetime,substring(convert(varchar,dateadd(year,-1,@dt),112),1,4)+'0701',112)

Open in new window

to get fiscal starting date
0
 
HuaMin ChenSystem AnalystCommented:
Please run the above separately
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
GPSPOWAuthor Commented:
Thanks

I tried using it in a select statement with various dates and it worked just like I want it.

Glen
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
GPSPOW- I just kicked out an article that deals specifically with Fiscal calendar planning --> SQL Server Calendar Table:  Fiscal Years.   Let me know if this helps you.  If yes, click on the 'Good Article' button and provide some feedback.  Thanks.
1
 
GPSPOWAuthor Commented:
Excellent article.

I will begin using this in my future programming projects.

Thanks

Glen
0
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.

All Courses

From novice to tech pro — start learning today.