| Author |
 Topic  |
|
|
senilssonli
Starting Member
7 Posts |
 Posted - 09/16/2004 : 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
India
437 Posts |
Posted - 09/16/2004 : 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
India
437 Posts |
Posted - 09/16/2004 : 04:36:25
|
Also,
Can use DECLARE @mytable (t_year int, T_Month int..
- Sekar |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 09/16/2004 : 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 - 09/16/2004 : 14:28:55
|
| Thank you guys, works very well. Now I know about temporary tables and the "SET NOCOUNT ON" was very handy. /Lars |
|
|
| |
Topic |
|
|
|
All datas of a month in same result set
Forum Locked
