Solved

SQL Fiscal Year Starting 7/1 - Ending 6/30

Posted on 2014-12-18
4
147 Views
Last Modified: 2015-09-22
Could you please help me create formulas for these -in SQL 2012:

FY Starting 7/1 - Ending 6/30

1) FY End Date of GETDATE()
2) FY End Date of Last year
3) FY Start Date - 10 years ago

Each of these should be in format m/d/yyyy - 7/1/2014

Thanks so much!
Scott
0
Comment
Question by:Scott Williams
  • 2
4 Comments
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
here

\
select 
CAST(cast(datepart(year,getdate()) as varchar)+'0630'  AS date) fye_this,
CAST(cast(datepart(year,getdate())-1 as varchar)+'0630'  AS date) fye_last,
CAST(cast(datepart(year,getdate())-10 as varchar)+'0701'  AS date) fys_past10year

fye_this	fye_last	fys_past10year
2014-06-30	2013-06-30	2004-07-01

Open in new window

0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
Comment Utility
and you can use convert(varchar,getdate(),101) to convert to mm/dd/yyyy

select 
convert(varchar,CAST(cast(datepart(year,getdate()) as varchar)+'0630'  AS date),101) fye_this,
convert(varchar,CAST(cast(datepart(year,getdate())-1 as varchar)+'0630'  AS date),101) fye_last,
convert(varchar,CAST(cast(datepart(year,getdate())-10 as varchar)+'0701'  AS date),101) fys_past10year

fye_this	fye_last	fys_past10year
06/30/2014	06/30/2013	07/01/2004

Open in new window

0
 

Author Closing Comment

by:Scott Williams
Comment Utility
Thanks a lot!!!
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Scott - 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, and if yes please click on the 'Good Article' button and provide some feedback.  Thanks.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now