Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

display the days for given month Expand / Collapse
Author
Message
Posted Tuesday, May 26, 2009 2:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 8, 2010 7:22 AM
Points: 46, 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
Post #723085
Posted Tuesday, May 26, 2009 2:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
Post #723094
Posted Tuesday, May 26, 2009 2:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 8, 2010 7:22 AM
Points: 46, Visits: 107
In my report i should not use any other tables.

I just wanted to get days to be displayed for given month
Post #723103
Posted Tuesday, May 26, 2009 3:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:43 PM
Points: 2,109, Visits: 5,419
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/
Post #723108
Posted Tuesday, May 26, 2009 3:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
Post #723110
Posted Tuesday, May 26, 2009 3:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
Post #723112
Posted Tuesday, May 26, 2009 3:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 8, 2010 7:22 AM
Points: 46, 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
Post #723113
Posted Tuesday, May 26, 2009 3:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



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
Post #723120
Posted Tuesday, May 26, 2009 4:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 8, 2010 7:22 AM
Points: 46, 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
Post #723145
Posted Tuesday, May 26, 2009 4:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
Post #723161
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse