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

# 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
1 Solution

Commented:
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

Data 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
``````
0

Commented:
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'
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

Commented:
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'
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

Assistant ConsultantCommented:
select convert(datetime, cast (year(getdate()) as varchar)  + '/' + '07' + '/' + '01')
0

Commented:
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.

## Featured Post

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