how to pass date as parameter to stored procedure

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

General SQL Server Forums

New to SQL Server Programming

how to pass date as parameter to stored procedure

Author

Topic

virkarchetan
Starting Member

2 Posts

Posted - 2011-04-14 : 02:27:41

how to pass date as parameter to stored procedure
create procedure new_proc (
@from_date datetime ,
@to_date datetime)
as
create table #temp(
tdate datetime,
count int default 0)

i want to pass @from date and @to_date how sholud i pass it!!!!!

@chetu

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-14 : 02:32:06

specify the date in YYYY-MM-DD format


exec new_proc @from_date = '2011-04-01', @to_date = '2011-04-30'

KH
[spoiler]Time is always against us[/spoiler]

virkarchetan
Starting Member

2 Posts

Posted - 2011-04-14 : 02:49:51

create procedure new_proc (
@from_date datetime ,
@to_date datetime)
as
create table #temp(
date datetime,
count int default 0)

while(@from_date <= @to_date)
begin
insert into #temp values('date','(@from_date)')
set @from_date = @from_date+1
end
--select * from #temp
update #temp set count=X.count
from(select date , count(1)as count from trade_detail
group by date)as X
where (#temp.date = X.date)

select date,count(1)as count from #temp
group by date

exec new_proc @from_date = '2011-11-01', @to_date = '2011-11-30'

this is my code it giveing the eroor

"Syntax error converting the varchar value '(@from_date)' to a column of data type int.
"

@chetu

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-04-14 : 03:04:03

this the place where its throwing error
'set @from_date = @from_date+1'

you cant just add +1 to a datetime colum...you need to use dateadd function to add +1 to it.

if you want to add 1 day to the date you must usse

select dateadd(dd,1,@from_date)
it add +1 day to you date

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-14 : 03:05:50

Your problem with this

insert into #temp values(@from_date,'(@from_date)')

How can you store 'Date' and in count '(@from_date)'

what output you need

and even you cant run like this

declare @from_date datetime
set @from_date='2011-11-01'+1
select @from_date
set @from_date=@from_date+1
select @from_Date

Raghu' S

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-04-14 : 03:08:32

quote:
Originally posted by raghuveer125

Your problem with this

insert into #temp values(@from_date,'(@from_date)')

How can you store 'Date' and in count '(@from_date)'

what output you need

Raghu' S

yes,raghu your point is correct...but see mine once

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-14 : 03:21:17

Ok here you can replace
set @from_date = @from_date+1
to this

set @from_date=convert(datetime,@from_date+1)
and more what you need output?

declare @from_date datetime
set @from_date='2011-11-01'
select @from_date
set @from_date=convert(datetime,@from_date+1)
select @from_Date

Raghu' S

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-14 : 03:34:12

See I don't know what are you looking for but
Is that you are looking for
alter procedure new_proc (
@from_date datetime ,
@to_date datetime)
as
create table #temp(
date datetime,
count int default 0)
Declare @i int
set @i=count(@from_Date)
while(@from_date <= @to_date)
begin
insert into #temp values(@from_date,@i)
--select * from #temp
set @from_date=convert(datetime,@from_date+1)
set @i=count(@from_Date)
end
--select * from #temp
update #temp set count=X.count
from(select date , count(1)as count from #temp
group by date)as X
where (#temp.date = X.date)
select date,count(1)as count from #temp
group by date

exec new_proc @from_date = '2011-11-01', @to_date = '2011-11-30'

Raghu' S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources