Financial Month

  • Comments posted to this topic are about the item Financial Month

    Thanks & Regards, Kartik M Kumar..

  • Please provide details 🙂

  • Am I missing something, or is the UDF code missing from the post?

  • Yes, the UDF code missing from the post, please update ...

  • Hi All,

    I donno how the UDF code was missed while publishing, I update the code as a new article, however I'm providing the same here also..

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Fn_FinMonth]') and xtype in (N'FN', N'IF', N'TF'))

    drop function [dbo].[Fn_FinMonth]

    go

    Create Function dbo.Fn_FinMonth(@MonthCode varchar(2))

    Returns Varchar(2)

    As

    /*------------------------------------------------------------------------------------------------------------------------------------------

    ** SQLVersion : SQL 2000

    ** Function: Fn_FinMonth

    ** Author : Kartik M

    ** DateTime: 29 January 2011 22:30

    ** Version: 1.2

    ** Purpose: To get the financial month from the respective calander month

    ** ToCheck:

    ** Changes: KM - 06 Feburary 2011 - Change the return type form Numeric to Varchar and add Leading Zeros to avoid problem while sorting

    ------------------------------------------------------------------------------------------------------------------------------------------*/

    /*

    -- Start of Debugging Stuff

    Declare @MonthCode varchar(2)

    Set @MonthCode = '02' -- Pass the Month Number as Input

    -- End of Debugging Stuff

    */

    Begin

    Declare @FinMonthC Varchar(2)

    Declare @IMonthCode int

    Set @IMonthCode = cast(@MonthCode as int)

    if @ImonthCode between 4 and 12

    Begin

    Set @FinMonthC = @IMonthCode-3

    Set @FinMonthC = Replicate('0', 2-len(@FinMonthC))+@FinMonthC

    End

    Else if @ImonthCode between 1 and 3

    Begin

    Set @FinMonthC = @IMonthCode+9

    Set @FinMonthC = Replicate('0', 2-len(@FinMonthC))+@FinMonthC

    End

    Return @FinMonthC

    End

    --Select dbo.Fn_FinMonth ('05')

    go

    Thanks & Regards, Kartik M Kumar..

  • Please refer above code...

    Thanks & Regards, Kartik M Kumar..

  • Following is simple query for the same.

    [font="Courier New"]DECLARE @Cal_Month AS INT

    SET @Cal_Month = 1

    SELECT @Cal_Month Cal_Month,ISNULL(NULLIF((@Cal_Month+9)%12,0),12) Fin_Month

    SET @Cal_Month = 3

    SELECT @Cal_Month Cal_Month,ISNULL(NULLIF((@Cal_Month+9)%12,0),12) Fin_Month

    SET @Cal_Month = 4

    SELECT @Cal_Month Cal_Month,ISNULL(NULLIF((@Cal_Month+9)%12,0),12) Fin_Month

    SET @Cal_Month = 12

    SELECT @Cal_Month Cal_Month,ISNULL(NULLIF((@Cal_Month+9)%12,0),12) Fin_Month[/font]

  • Thanks for posting this, I was just about to write something similar when I came across your post.

    There is one small thing that I wanted to mention: In the United States, companies have the option of selecting their own fiscal year. For example, my companies fiscal year is October 1st to September 30th of the following year.

    The only proviso is that a company may not change their fiscal year without informing the regulatory authorities (IRS, SEC, etc).

    So, I'm going to adapt your code to my companies fiscal year and call it good. Thanks!

  • DECLARE @Varmonth CHAR(2)

    SELECT @Varmonth = 04

    select @Varmonth =datediff(mm,'1899/04/01',DATEADD(mm,CAST(@Varmonth AS INT) ,0))%12 +CASE WHEN CAST(@Varmonth AS INT) = 3 THEN 12 ELSE 0 END

    select @Varmonth = REPLICATE('0',2-LEN(@Varmonth))+@Varmonth

    select @Varmonth

    Regards,
    Mitesh OSwal
    +918698619998

  • Thanks for the script.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply