SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


display the days for given month


display the days for given month

Author
Message
purushotham.k9
purushotham.k9
SSC Eights!
SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)

Group: General Forum Members
Points: 840 Visits: 107
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
Florian Reischl
Florian Reischl
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15527 Visits: 3934
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


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
purushotham.k9
purushotham.k9
SSC Eights!
SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)

Group: General Forum Members
Points: 840 Visits: 107
In my report i should not use any other tables.

I just wanted to get days to be displayed for given month
Adi Cohn
Adi Cohn
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14820 Visits: 6621
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/
Florian Reischl
Florian Reischl
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15527 Visits: 3934
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/


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Florian Reischl
Florian Reischl
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15527 Visits: 3934
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))




The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
purushotham.k9
purushotham.k9
SSC Eights!
SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)

Group: General Forum Members
Points: 840 Visits: 107
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
Florian Reischl
Florian Reischl
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15527 Visits: 3934
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


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
purushotham.k9
purushotham.k9
SSC Eights!
SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)

Group: General Forum Members
Points: 840 Visits: 107
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
Florian Reischl
Florian Reischl
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15527 Visits: 3934
Copy my above script into SSMS and execute...


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search