Post reply

Month and Year Calculation

  • November 2, 2012 at 12:50 pm

    #264987

    Hello Everyone,

    I do have fromdate and Todate and I want to geterate temp table or (table ) between fromdate to datedate.

    how can i do with function.

    I am trying to do with this function .

    Create FUNCTION [Common].[f_getMonthYear]

    (

    @FromDate Date,

    @ToDate Date

    )

    Returns @Values Table

    (

    Year int,Month int)

    AS

    BEgin

    [font="Arial"]

    Can Any one write script here.[/font]

    END

    For Example

    @fromdate = '2011-05-01'

    @Todate= '2012-05-01'

    I want a output like

    Month Year

    5 2011

    6 2011

    7 2011

    8 2011

    9 2011

    10 2011

    11 2011

    12 2011

    1 2012

    2 2012

    3 2012

    4 2012

    Thanks

    Bhavesh

  • November 2, 2012 at 12:54 pm

    #1555635

    Do you need to create an UDF?

    Why can't you use DATEDIFF?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 2, 2012 at 12:57 pm

    #1555637

    Yes, because I need to use this functin in 20-25 stored procedure.

    Can you explain me how can i do with Datediff ?

  • November 2, 2012 at 1:10 pm

    #1555639

    bhaveshp.dba (11/2/2012)

    Yes, because I need to use this functin in 20-25 stored procedure.

    Can you explain me how can i do with Datediff ?

    Don't use a UDF. Create a calendar table. Why would you calculate this every time when you can do it once in a table and just reference the table?

    Jared
    CE - Microsoft

  • November 2, 2012 at 1:16 pm

    #1555640

    I need to geterate table within fromdate and Todate.

    and Use this table with left join to other table.

    Select m.month,v.year, ID, Name from #Month m

    lett join View_Data v on v.month = m.month and v.year= m.year

    My Another table view_Date missing some month and My stored procedure should return all month within fromdate and todate .

    If I am missing month, My Report is not corrent.

    Thanks

    Bhavesh

  • November 2, 2012 at 1:21 pm

    #1555642

    bhaveshp.dba (11/2/2012)

    I need to geterate table within fromdate and Todate.

    and Use this table with left join to other table.

    Select m.month,v.year, ID, Name from #Month m

    lett join View_Data v on v.month = m.month and v.year= m.year

    My Another table view_Date missing some month and My stored procedure should return all month within fromdate and todate .

    If I am missing month, My Report is not corrent.

    Thanks

    Bhavesh

    See below:

    Don't use a UDF. Create a calendar table. Why would you calculate this every time when you can do it once in a table and just reference the table?

    That means to build a table that has everything you need. Don't calculate it each time. If you already have a table that is missing information, create a new one that is not...

    Jared
    CE - Microsoft

  • November 2, 2012 at 1:23 pm

    #1555643

    it's very difficult to generate new one. because it;s already generate by stored procedure and update record everynight.

    I can not make in this table now.

  • November 2, 2012 at 1:25 pm

    #1555644

    bhaveshp.dba (11/2/2012)

    it's very difficult to generate new one. because it;s already generate by stored procedure and update record everynight.

    I can not make in this table now.

    I don't understand... If you can create a UDF, you can create a new table.

    Jared
    CE - Microsoft

  • November 2, 2012 at 1:32 pm

    #1555647

    I got it.

    Thanks

    Bhavesh

  • November 2, 2012 at 4:09 pm

    #1555686

    bhaveshp.dba (11/2/2012)

    I got it.

    Thanks

    Bhavesh

    Can we see the code you ended up with please? Thanks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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