• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3721
  • Last Modified:

How do I determine the first day of the current financial year?

I need to include a condition in my query based on the first day of the current Australian financial year.  For instance, currently the date would be 1st of July, 2010.

How do I determine this date?
0
glit
Asked:
glit
1 Solution
 
Pratima PharandeCommented:
I don't think such built in fucntion is available ....
May be you can try this
create one master table whenere you can store the two values like day(sday) and month(smonth)

and then add current year to it to create date like this


select convert(datetime, sday +'/' + smonth + '/' + year(gatedate(),1) from newtable
0
 
SharathData EngineerCommented:
try something like this.
DECLARE @date DATETIME 

SET @date = '08/01/2011' 
SELECT CASE 
         WHEN RIGHT(CONVERT(VARCHAR(8), @date, 112), 4) <= '0601' THEN 
         CONVERT(VARCHAR, DATEPART(YY, @date)-1) + '0601' 
         ELSE CONVERT(VARCHAR, DATEPART(YY, @date)) + '0601' 
       END  
-- 20110601
SET @date = '04/01/2011' 
SELECT CASE 
         WHEN RIGHT(CONVERT(VARCHAR(8), @date, 112), 4) <= '0601' THEN 
         CONVERT(VARCHAR, DATEPART(YY, @date)-1) + '0601' 
         ELSE CONVERT(VARCHAR, DATEPART(YY, @date)) + '0601' 
       END  
-- 20100601

Open in new window

0
 
MadRacer1Commented:
DECLARE @FM INT
DECLARE @M INT
SET @FM=4 -- Month number from where Fiscal year is starting. By default 4- April. If you want to change then change number

SET @M=MONTH(GETDATE()) -- Get Current Month Number

IF @M >@FM  -- Depending on Current Month Fiscal year is considered  i.e. FY  2010-2011 or 2011-2012
 BEGIN
  SELECT  CONVERT(VARCHAR,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-@M+@FM,0),101) AS 'First Day of Fiscal Year'
 ,CONVERT(VARCHAR,DATEADD(s,-1,DATEADD(YEAR,+1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-@M+@FM,0))),101) AS 'Last Day of Fiscal Year'
 END
 ELSE
 BEGIN
 
  SELECT  CONVERT(VARCHAR,DATEADD(yy,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-@M+@FM,0)),101) AS 'First Day of Fiscal Year'
 ,CONVERT(VARCHAR,DATEADD(s,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-@M+@FM,0)),101) AS 'Last Day of Fiscal Year'
 END

 I think this will solve the purpose... :)
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MadRacer1Commented:
Dear GLIT,

here is query to calculate First and Last day of fiscal year.

In addition to that i have added previlage to change staring of fiscal year. As in some cases(Business scenarios) fiscal year starts on July instead of April

If needed then just change @FM  from 4(i.e. April) to your respected Month Number

Finally SQL query is as below

DECLARE @FM INT
DECLARE @M INT
SET @FM=4 -- Month number from where Fiscal year is starting. By default 4- April. If you want to change then change number

SET @M=MONTH(GETDATE()) -- Get Current Month Number

IF @M >@FM  -- Depending on Current Month Fiscal year is considered  i.e. FY  2010-2011 or 2011-2012
 BEGIN
  SELECT  CONVERT(VARCHAR,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-@M+@FM,0),101) AS 'First Day of Fiscal Year'
 ,CONVERT(VARCHAR,DATEADD(s,-1,DATEADD(YEAR,+1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-@M+@FM,0))),101) AS 'Last Day of Fiscal Year'
 END
 ELSE
 BEGIN
 
  SELECT  CONVERT(VARCHAR,DATEADD(yy,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-@M+@FM,0)),101) AS 'First Day of Fiscal Year'
 ,CONVERT(VARCHAR,DATEADD(s,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-@M+@FM,0)),101) AS 'Last Day of Fiscal Year'
 END

 I think this will solve the purpose... :)


0
 
Alpesh PatelAssistant ConsultantCommented:
select convert(datetime, cast (year(getdate()) as varchar)  + '/' + '07' + '/' + '01')
0
 
GhunaimaCommented:
Use this formula

'01-Jul-'+convert(varchar,case when month(getdate())>=7 then year(getdate()) else year(getdate())-1 end)
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now