SP Help - 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)

SP Help

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-09-29 : 03:49:36

Hello friends..

i have created a sp to find the total no of days for a perticular month & based on the year...... and here is the sp

create proc datefun
(
@month int,
@year int
)
as
begin
declare @final datetime
declare @No int
set @final=(select convert(varchar,@month)+ '-' + convert(Varchar,01)+ '-' + convert(varchar,@year))
set @No=(select CASE WHEN MONTH(@final) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@final) IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (YEAR(@final) % 4 = 0 AND
YEAR(@final) % 100 != 0) OR
(YEAR(@final) % 400 = 0)
THEN 29
ELSE 28
END
END)

select @No
end

is this the correct way to do....or is there any other alternate way to do this........

Thanks in advance

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 04:01:43

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-09-29 : 04:09:42

how can i get total noof days for a perticular month based on year using this sp...
if its an leap year I need to get 29 days else 28 days...
is that possible.....

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 04:13:28

[code]create proc datefun
(
@month int,
@year int
)
as
declare @date datetime
select @date=cast(@month as varchar(2)) + '/'+ '01/'+cast(@year as varchar(4))
select @date
select datediff(dd,@date,dateadd(mm,1,@date)) AS numberofdays
go[/code]

the above sp will give you days
just try passing month,year values
Exec datefun 02,2008
Exec datefun 02,2009
Exec datefun 11,2008...

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-09-29 : 06:53:32

can i able to assign this stored procedure to a variable in another stored procedure.....
while doing i am getting error

create proc prcdatefun
(
@month int,
@year int
)
as
begin
declare @totaldays int
set @totaldays = exec datefun @month,@year
select @totaldays
end

i am getting error msg ''''

Msg 156, Level 15, State 1, Procedure prcsaldeduction, Line 11
Incorrect syntax near the keyword 'exec'.

how can i rectify this ..please help me

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 07:04:11

quote:
Originally posted by shaik.zakeer

can i able to assign this stored procedure to a variable in another stored procedure.....
while doing i am getting error

create proc prcdatefun
(
@month int,
@year int
)
as
begin
declare @totaldays int
set @totaldays = exec datefun @month,@year
select @totaldays
end

i am getting error msg ''''

Msg 156, Level 15, State 1, Procedure prcsaldeduction, Line 11
Incorrect syntax near the keyword 'exec'.

how can i rectify this ..please help me

Thanks

Zakeer Sk

for that just create a function rather than procedure

create function datefun
(
@month int,
@year int
)
returns int
as
declare @date datetime
select @date=cast(@month as varchar(2)) + '/'+ '01/'+cast(@year as varchar(4))
select @days=datediff(dd,@date,dateadd(mm,1,@date)) 
return @days
go

and use it like this

create proc prcdatefun
(
@month int,
@year int
)
as 
begin
declare @totaldays int
set @totaldays = dbo.datefun(@month,@year)
select @totaldays
end

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-09-29 : 07:21:21

gr8 work buddy,,,,thanks alot

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 07:23:32

quote:
Originally posted by shaik.zakeer

gr8 work buddy,,,,thanks alot

Thanks

Zakeer Sk

you're welcome

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 07:25:03

And if you want an inline version

alter function dbo.datefun
(
	@month int,
	@year int
)
returns tinyint
as
begin
	return	datediff(day, dateadd(month, @month + 12 * @year - 22801, 0), dateadd(month, @month + 12 * @year - 22800, 0))
end

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-29 : 10:23:49

or

declare @month int,@year int
select @month=2, @year=2000
select day(dateadd(month,1,dateadd(year,@year-1900,dateadd(month,@month-1,0)))-1)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources