All datas of a month in same result set

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

All datas of a month in same result set

Author

Topic

senilssonli
Starting Member

7 Posts

Posted - 2004-09-16 : 03:46:31

I have built a stored procedure, where I can send in a data and then get back all days in that month. Now I want the result in the same result set. Is there a way of doing that?

----------------------------------------------------------------
CREATE PROCEDURE sp_GetAllDaysOfMonth

@input_date datetime='2004-09-01'
as
declare @counter int set @counter=0
set datefirst 1

while datepart(month,@input_date)=datepart(month,DATEADD(day,@counter,@input_date))
begin
select datepart(year,DATEADD(day,@counter,@input_date)) as year,
datepart(month,DATEADD(day,@counter,@input_date)) as Month,
datepart(day,DATEADD(day,@counter,@input_date)) as DayInMonth,
datename(month,DATEADD(day,@counter,@input_date)) as MonthName,
datepart(weekday,DATEADD(day,@counter,@input_date)) as DayInWeek,
datepart(week,DATEADD(day,@counter,@input_date)) as WeekNo,
datepart(dayofyear,DATEADD(day,@counter,@input_date)) as DayInYear
set @counter=@counter+1
end
GO

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-09-16 : 04:34:42

Can USE Temporary Table.

----------------------------------------------------------------
CREATE PROCEDURE sp_GetAllDaysOfMonth

@input_date datetime='2004-09-01'
as
declare @counter int set @counter=0
set datefirst 1

Create table #temp (t_year int, T_Month int, DayInMonth int, MonthName varchar(20), DayInWeek int, WeekNo int, DayInYear int )

while datepart(month,@input_date)=datepart(month,DATEADD(day,@counter,@input_date))
begin

Insert into #temp (t_year, T_Month, DayInMonth, MonthName, DayInWeek, WeekNo, DayInYear )
select
datepart(year,DATEADD(day,@counter,@input_date)) as year,
datepart(month,DATEADD(day,@counter,@input_date)) as Month,
datepart(day,DATEADD(day,@counter,@input_date)) as DayInMonth,
datename(month,DATEADD(day,@counter,@input_date)) as MonthName,
datepart(weekday,DATEADD(day,@counter,@input_date)) as DayInWeek,
datepart(week,DATEADD(day,@counter,@input_date)) as WeekNo,
datepart(dayofyear,DATEADD(day,@counter,@input_date)) as DayInYear

set @counter=@counter+1
end

Select * from #temp
drop table #temp
GO

- Sekar

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-09-16 : 04:36:25

Also,

Can use DECLARE @mytable (t_year int, T_Month int..

- Sekar

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-16 : 08:25:07

If you have a tally ( numbers ) table it can be handy,
here I create one on the fly...


SET NOCOUNT ON
SET DATEFIRST 1

DECLARE @input_date DATETIME
DECLARE @end_date DATETIME

-- 1st day of this month
SET @input_date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)
-- last day of this month
SET @end_date = DATEADD(MONTH,1,@input_date)- 1

-- create a tally table with all days from start to end...
CREATE TABLE #tally(daynr INT NOT NULL)

INSERT #tally
SELECT CAST(@input_date AS INT)

WHILE ( SELECT MAX(daynr) FROM #tally ) < CAST( @end_date AS INT ) -- cast not really necessary
	INSERT #tally(daynr)
	SELECT MAX(daynr) + 1 FROM #tally

-- select the dateinfo from tally table
SELECT
	YEAR(daynr)		AS [Year],
	MONTH(daynr)		AS [Month],
	DAY(daynr)		AS [DayInMonth],
	DATEPART(MONTH,daynr)	AS [MonthName],
	DATEPART(WEEKDAY,daynr)	AS [DayInWeek],
	DATEPART(WEEK,daynr)	AS [WeekNo],
	DATEPART(DAYOFYEAR,daynr) AS [DayInYear]
FROM
	#tally


DROP TABLE #tally

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

senilssonli
Starting Member

7 Posts

Posted - 2004-09-16 : 14:28:55

Thank you guys, works very well. Now I know about temporary tables and the "SET NOCOUNT ON" was very handy. /Lars

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources