• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3187
  • 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 recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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