Wednesday, May 11, 2011

Making Financial Year Function In SQL Server

Hi,
Many times we required to add financial year with our invoice or po number to make it suitable for client as like PO/2011-12/04/001  here 2011-12 is current financial year. For this purpose we should make a function for getting current financial year, which can be used in insert update or select statement.

Here is a function which returns financial year as varchar for the given datetime or we can use getdate() function for getting current date financial year.


CREATE FUNCTION [dbo].[FinencialYear]
(@Date DATETIME)
RETURNS VARCHAR(7) AS
BEGIN
DECLARE @RetVal varchar(7)
Declare @Month int
Declare @Year int
Select @Month=Month(@Date)
Select @Year=Year(@Date)
-- Assuming that the financial year is starting from april
if @Month>3
 Begin
Set @RetVal=cast(@Year As varchar(10))+'-'+cast(right('0' + rtrim(@Year+1),2) as varchar(2))
End
Else
Begin
Set @RetVal=cast(@Year-1 as varchar(10))+'-'+cast(right('0' + rtrim(@Year),2) as varchar(2));
End
Return @RetVal
End

We can use this function as ordinary function as Select dbo.FinencialYear(getdate()) or replace getdate() with any date for which you want to get financial year.

Thanks..

13 comments:

  1. I am extremely impressed along with your writing abilities
    and also with the structure for your weblog. Is this a paid
    subject matter or did you modify it your self? Either
    way stay up the excellent quality writing, it is uncommon to see a great blog like this
    one these days..

    Here is my web blog ... Nitro Focus No3 Reviews

    ReplyDelete
  2. What's up colleagues, how is everything, and what you desire to say concerning this piece of writing, in my view its in fact amazing in favor of me.

    Here is my website ... Xength X1 Free Trial

    ReplyDelete
  3. Your style is very unique compared to other people I have read stuff from.
    Thank you for posting when you've got the opportunity, Guess I'll just book mark this blog.


    Review my webpage - Green coffee reviews

    ReplyDelete
  4. Taste is the key here because you want something
    that you’re going to be able to drink every single day.
    During your resistance training workouts, you should practice fascial
    stretching. How to build muscle fast for men is through training
    in the gym or field.

    Here is my website ... Power Pump XL Review

    ReplyDelete
  5. obviously like your web-site but you have to take
    a look at the spelling on several of your posts. Several
    of them are rife with spelling problems and I
    find it very bothersome to inform the truth then again I will certainly come back again.


    Review my weblog: Pureberry Max Trial

    ReplyDelete
  6. I enjoy reading through an article that can make men and
    women think. Also, thanks for allowing me to comment!

    Enduros Male Enhancement Review

    ReplyDelete
  7. This post is truly a pleasant one it assists new net people, who are wishing for blogging.


    Reverge

    ReplyDelete
  8. Amazing blog! Is your theme custom made or did you download it from somewhere?
    A theme like yours with a few simple adjustements would really make my blog stand out.
    Please let me know where you got your design. Kudos

    my web site ... garcinia cambogia for weight loss

    ReplyDelete
  9. You actually make it seem so easy with your presentation but I find this matter to be
    actually something that I think I would never understand. It seems too complicated
    and very broad for me. I am looking forward for your next post, I will try
    to get the hang of it!

    Stop by my webpage; Lipo Slim

    ReplyDelete
  10. Hey There. I found your blog using msn. This is an extremely well
    written article. I'll make sure to bookmark it and come back to read more of your useful information. Thanks for the post. I'll definitely
    return.

    My blog post :: pearl x reviews

    ReplyDelete
  11. I like what you guys tend to be up too. This sort of clever
    work and reporting! Keep up the superb works guys I've incorporated you guys to blogroll.

    Visit my blog - working online from home

    ReplyDelete
  12. Wow! After all I got a blog from where I be capable of truly obtain valuable data concerning my
    study and knowledge.

    Check out my website Revolyn reviews

    ReplyDelete