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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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.04 seconds. Powered By: Snitz Forums 2000