SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 All datas of a month in same result set
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

senilssonli
Starting Member

7 Posts

Posted - 09/16/2004 :  03:46:31  Show Profile
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

India
437 Posts

Posted - 09/16/2004 :  04:34:42  Show Profile
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
Go to Top of Page

samsekar
Constraint Violating Yak Guru

India
437 Posts

Posted - 09/16/2004 :  04:36:25  Show Profile
Also,

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

- Sekar
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 09/16/2004 :  08:25:07  Show Profile
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 */
Go to Top of Page

senilssonli
Starting Member

7 Posts

Posted - 09/16/2004 :  14:28:55  Show Profile
Thank you guys, works very well. Now I know about temporary tables and the "SET NOCOUNT ON" was very handy. /Lars
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000