February 15, 2011 at 8:15 pm
Comments posted to this topic are about the item Financial Month
Thanks & Regards, Kartik M Kumar..
February 15, 2011 at 11:54 pm
Please provide details 🙂
February 16, 2011 at 12:12 pm
Am I missing something, or is the UDF code missing from the post?
February 16, 2011 at 11:58 pm
Yes, the UDF code missing from the post, please update ...
February 17, 2011 at 3:17 am
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..
February 18, 2011 at 6:57 pm
Please refer above code...
Thanks & Regards, Kartik M Kumar..
March 12, 2011 at 10:34 pm
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]
April 8, 2011 at 3:35 pm
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!
April 27, 2011 at 5:25 am
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
May 17, 2016 at 6:52 am
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