Solved

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

Posted on 2014-12-18
4
153 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
ID: 40507566
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
ID: 40507570
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
ID: 40507644
Thanks a lot!!!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40988979
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL replication over high latency link 10 60
IIF in access query 19 26
Dynamic SQL select query 4 38
Update one table with results from another table in SQL 6 27
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

832 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