asked on

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

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Peter Chan

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

Peter Chan

Please run the above separately

GPSPOW

ASKER

Thanks

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

Glen

Jim Horn

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.

GPSPOW

ASKER

Excellent article.

I will begin using this in my future programming projects.

Thanks

Glen