Sunday, July 29, 2012

SQL Server – How To Get Financial Year and How to get First Date Of Current Month


Question: How To Get Current Financial Year?
Use the below script to get the current or any year of financial year.

 DECLARE @6 smalldatetime
 DECLARE @8 varchar(20)
 DECLARE @9 varchar(20)
 SET @8  = CAST(YEAR(GETDATE()) AS int)
 SET @6 = CAST(MONTH(GETDATE())AS int)

 if(@6>3)
 begin
 SET @8=CAST(YEAR(GETDATE()) AS int) +1
 SET @9=CAST(YEAR(GETDATE()) AS int)
 SET @9 =@9 +'-'+ @8 
 SELECT @9 AS Financial_Year
 end
 else if (@6<4)
 begin
 SET @8=CAST(YEAR(GETDATE()) AS int) -1
 SET @9= CAST(YEAR(GETDATE()) AS int)
 SET @9 =@8 +'-'+ @9 
 SELECT @9 AS Financial_Year
 end

Output:

Financial_Year
--------------------
2012-2013


Question: How to get First Date Of Current Month?

DECLARE @1 varchar(20)
DECLARE @2 varchar(20)
DECLARE @3 varchar(20)
DECLARE @4 varchar(20)
DECLARE @5 varchar(20)

 SET @1 =GETDATE()
 SET @2 =STUFF (@1,4,3,'')
 SET @3 =SUBSTRING (@2,1,4)
 SET @4 =SUBSTRING (@2,4,LEN(@2))
 SET @5= @3 + '01'+ @4

SELECT @5 AS First_Date_Of_This_month

Output:

First_Date_Of_This_month
------------------------
Jul 01 2012  9:57PM




Like and Share to SQL Integrity Blog

4 comments:

  1. how to get the first date of the current financial year from current date???

    ReplyDelete
  2. select cast(YEAR(dateadd(month,-3,'2012-06-11')) as varchar)+' -'
    + cast(year(dateadd(month, 9, '2012-09-01')) as varchar)

    ReplyDelete
  3. Thanks for sharing these valuable script

    ReplyDelete

Thank You !!!!