want day wise data - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

want day wise data

Author

Topic

deepakugale
Starting Member

33 Posts

Posted - 2010-05-06 : 07:57:21

Hi Experts ,

I want to write a query which will take month and year as an input,and will give result as day wise row
For eg . If "05" and "2010" as input then output should be like

05/01/2010 1
05/02/2010 2
05/03/2010 3
05/04/2010 4
05/05/2010 5
.
.
.
.
.
.
05/31/2010 31

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-06 : 08:01:36

You mean you want to populate a calander ?

Regards,
bohra

I am here to learn from Masters and help new bees in learning.

apodemus
Starting Member

30 Posts

Posted - 2010-05-06 : 08:13:36

create procedure GetDays (@Month int, @Year int)
AS
BEGIN
DECLARE @Days TABLE (day datetime)
DECLARE @day int
set @day = 1

WHILE @day <= 31
begin try

insert into @Days (day)
select cast(@month as varchar(2)) + '/'+CAST(@Day as varchar(2))+ '/' + cast(@year as varchar(10))
set @day = @day + 1;
end try
begin catch
select * from @Days
return

end catch
select * from @Days

END

exec GetDays 2, 2012

apodemus

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-06 : 08:18:49

Try this:
Create PROCEDURE dbo.usp_GenerateCal
@Month Varchar(10), @Year Varchar(4)
--@startdate DATETIME ,@enddate DATETIME ,@period int, @value1 int, @value2 int
AS
BEGIN
Declare @startdate datetime
Declare @EndDate datetime
Declare @DayDiff Int
Declare @CalTable Table (RunDate datetime)

Print @Year + '-' +@Month + '-' + '01'

Set @startdate = Convert(Datetime, @Year + '-' +@Month + '-' + '01',120)
print @startdate
Set @DayDiff = Datediff(dd,@startdate,DateAdd(M,1,@startdate) )
print @DayDiff

Set @enddate = DateAdd(dd,@DayDiff - 1, @startdate )

;WITH DatesCTE(CurrentDate) AS
(
SELECT @startdate AS CurrentDate
UNION ALL
SELECT DATEADD(day,1,CurrentDate)
FROM DatesCTE
WHERE CurrentDate < @enddate
)

INSERT INTO @CalTable (RunDate)
SELECT CurrentDate FROM DatesCTE
OPTION (MAXRECURSION 0)

Select * from @CalTable

END
GO

--Exec usp_GenerateCal '05','2010'

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources