Post reply

display the days for given month

  • May 26, 2009 at 2:28 am

    #132574

    function or procedure to display the days for given month. for example for the month of May i have to get the days from 1 to 31(like 1,2,3,4............31 like that)..

    Any help..it's very urgent

  • May 26, 2009 at 2:48 am

    #999755

    Hi

    I would advice to use a Tally table:

    DECLARE @Month DATETIME

    SELECT @Month = '2009-02-01'

    SELECT

    N

    FROM Tally

    WHERE

    N <= DATEDIFF(DAY, @Month, DATEADD(MONTH, 1, @Month))

    For sure, this could also be done with a Calendar table.

    Greets

    Flo

  • May 26, 2009 at 2:57 am

    #999762

    In my report i should not use any other tables.

    I just wanted to get days to be displayed for given month

  • May 26, 2009 at 3:08 am

    #999767

    Here is another way of doing it using a CTE instead of a table, but I don't understand the request that the report shouldn't use another table. Who cares what happened behind the scenes if the report brings the requested data and has no performance problems?

    declare @date datetime

    set @date = '20090501';

    with DaysInMonth as (

    select @date as Date

    union all

    select dateadd(dd,1,Date)

    from DaysInMonth

    where month(date) = month(@Date))

    select * from DaysInMonth where month(date) = month(@Date)

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • May 26, 2009 at 3:12 am

    #999769

    I confirm Adi. A Tally/Numbers table is a great helper for many things. It requires about 150kb disk usage and the profit is really huge.

    Have look to this article just to see some of the gains:

    http://www.sqlservercentral.com/articles/T-SQL/62867/

  • May 26, 2009 at 3:17 am

    #999771

    You can also use an inline Tally table:

    DECLARE @Month DATETIME

    SELECT @Month = '2009-02-01'

    ; WITH

    t1 AS (SELECT 1 N UNION ALL SELECT 1 N), -- 2

    t2 AS (SELECT 1 N FROM t1 x, t1 y), -- 4

    t3 AS (SELECT 1 N FROM t2 x, t2 y), -- 16

    Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t3 x, t3 y) -- 256

    SELECT

    N

    FROM Tally

    WHERE

    N <= DATEDIFF(DAY, @Month, DATEADD(MONTH, 1, @Month))

  • May 26, 2009 at 3:18 am

    #999772

    i have one small request instead of getting to display all i just wanted to display like 1,2,3,4....and so on 31 thats it.

    Thanks much

  • May 26, 2009 at 3:29 am

    #999776

    If you want a comma separated list of the numbers use a quirky update syntax (or a FOR XML, which performs better but is more complicated).

    Here the quirky update:

    DECLARE @Month DATETIME

    SELECT @Month = '2009-02-01'

    DECLARE @Ret VARCHAR(200)

    ; WITH

    t1 AS (SELECT 1 N UNION ALL SELECT 1 N), -- 2

    t2 AS (SELECT 1 N FROM t1 x, t1 y), -- 4

    t3 AS (SELECT 1 N FROM t2 x, t2 y), -- 16

    Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t3 x, t3 y) -- 256

    SELECT

    @Ret = ISNULL(@Ret + ', ', '') + CONVERT(VARCHAR(4), N)

    --N

    FROM Tally

    WHERE

    N <= DATEDIFF(DAY, @Month, DATEADD(MONTH, 1, @Month))

    PRINT @Ret

    No real table, no storage, just numbers :hehe:

  • May 26, 2009 at 4:11 am

    #999796

    can you please tell me how do i get only dates like 1,2,3 and so on 31. i don't want to display the time stamp, month and year and all...

    thanks mcuh

  • May 26, 2009 at 4:52 am

    #999809

    Copy my above script into SSMS and execute...

  • October 27, 2009 at 9:05 pm

    #1071543

    My problem is something along this lines. . I want a stored procedures that

    1) takes input as year. returns a list of last ten years including input year +1

    example, input 2009 should return

    2010

    2009

    2008

    .

    .

    2000

    Thanks

  • October 27, 2009 at 9:59 pm

    #1071548

    slight correction. it should return for the current year + 1 and not for any input parameter .

    example current year is 2009, so it should return a list like this

    2010

    2009

    2008

    .

    .

    .

    2000

    thanks

  • October 27, 2009 at 10:07 pm

    #1071550

    slight correction. it should return for the current year + 1 and not for any input parameter .

    example current year is 2009, so it should return a list like this

    2010

    2009

    2008

    .

    .

    .

    2000

    thanks

  • October 27, 2009 at 10:10 pm

    #1071551

    slight correction. it should return for the current year + 1 and not for any input parameter .

    example current year is 2009, so it should return a list like this

    2010

    2009

    2008

    .

    .

    .

    2000

    thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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