SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to pass date as parameter to stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

virkarchetan
Starting Member

India
2 Posts

Posted - 04/14/2011 :  02:27:41  Show Profile  Reply with Quote
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)

Singapore
17642 Posts

Posted - 04/14/2011 :  02:32:06  Show Profile  Reply with Quote
specify the date in YYYY-MM-DD format


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



KH
Time is always against us

Go to Top of Page

virkarchetan
Starting Member

India
2 Posts

Posted - 04/14/2011 :  02:49:51  Show Profile  Reply with Quote

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
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
655 Posts

Posted - 04/14/2011 :  03:04:03  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
Go to Top of Page

raghuveer125
Constraint Violating Yak Guru

India
285 Posts

Posted - 04/14/2011 :  03:05:50  Show Profile  Reply with Quote
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

Edited by - raghuveer125 on 04/14/2011 03:09:58
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
655 Posts

Posted - 04/14/2011 :  03:08:32  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
Go to Top of Page

raghuveer125
Constraint Violating Yak Guru

India
285 Posts

Posted - 04/14/2011 :  03:21:17  Show Profile  Reply with Quote
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

Edited by - raghuveer125 on 04/14/2011 03:21:38
Go to Top of Page

raghuveer125
Constraint Violating Yak Guru

India
285 Posts

Posted - 04/14/2011 :  03:34:12  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000