Financial Month

  • Kartik M

    SSChasing Mays

    Points: 624

    Comments posted to this topic are about the item Financial Month

    Thanks & Regards, Kartik M Kumar..

  • varshney4u

    Old Hand

    Points: 361

    Please provide details 🙂

  • UMG Developer

    SSChampion

    Points: 13482

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

  • varshney4u

    Old Hand

    Points: 361

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

  • Kartik M

    SSChasing Mays

    Points: 624

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

  • Kartik M

    SSChasing Mays

    Points: 624

    Please refer above code...

    Thanks & Regards, Kartik M Kumar..

  • ais.mech

    SSC Rookie

    Points: 37

    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]

  • hjackson67

    Valued Member

    Points: 62

    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!

  • Mitesh Oswal

    SSCrazy

    Points: 2798

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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