ALTER procedure [dbo].[anlookup] 
@datebegin datetime,
@dateend datetime,
@state varchar(30)
as begin


select <columnList>
from ANInventorymm
where <YourDateColumn> >= @datebegin
and   <YourDateColumn> < dateadd(day, +1, @dateend)

go

exec anlookup '2008-07-17', '2008-07-17', 'california'

quote:

---

Originally posted by fuzzyip

I have an easy stored procedure

ALTER procedure [dbo].[anlookup]
@datebegin char(20),
@dateend char(20),
@state varchar(30)
as begin

select *
from ANInventorymm
where max between '@datebegin 0:00:01 am' and '@dateend 23:59:59 pm' and city like '%@state%'

end


The max column in table ANInventorymm is in a datetime format where all the dates look like this, '7/17/2008 2:42:31 AM'

When I run the stored proc with something like this...

anlookup '7/17/2008', '7/17/2008', 'california'

i get,

Msg 241, Level 16, State 1, Procedure anlookup, Line 7
Conversion failed when converting datetime from character string.



Anyone have any insight on this?

---

quote:

---

Originally posted by fuzzyip

I have an easy stored procedure

ALTER procedure [dbo].[anlookup]
@datebegin char(20),
@dateend char(20),
@state varchar(30)
as begin

select *
from ANInventorymm
where max between '@datebegin 0:00:01 am' and '@dateend 23:59:59 pm' and city like '%@state%'

end


The max column in table ANInventorymm is in a datetime format where all the dates look like this, '7/17/2008 2:42:31 AM'

When I run the stored proc with something like this...

anlookup '7/17/2008', '7/17/2008', 'california'

i get,

Msg 241, Level 16, State 1, Procedure anlookup, Line 7
Conversion failed when converting datetime from character string.



Anyone have any insight on this?

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by fuzzyip

I have an easy stored procedure

ALTER procedure [dbo].[anlookup]
@datebegin char(20),
@dateend char(20),
@state varchar(30)
as begin

select *
from ANInventorymm
where max between '@datebegin 0:00:01 am' and '@dateend 23:59:59 pm' and city like '%@state%'

end


The max column in table ANInventorymm is in a datetime format where all the dates look like this, '7/17/2008 2:42:31 AM'

When I run the stored proc with something like this...

anlookup '7/17/2008', '7/17/2008', 'california'

i get,

Msg 241, Level 16, State 1, Procedure anlookup, Line 7
Conversion failed when converting datetime from character string.



Anyone have any insight on this?

---

no need to append time like that, just use

select *
from ANInventorymm
where max >@datebegin and max <dateadd(d,1,@dateend)
and city like '%'+@state+'%'

---

quote:

---

Originally posted by TG

I assume your date column is of a DATETIME datatype ? Is your datacolumn named: [max] ?

try this:

ALTER procedure [dbo].[anlookup] 
@datebegin datetime,
@dateend datetime,
@state varchar(30)
as begin


select <columnList>
from ANInventorymm
where <YourDateColumn> >= @datebegin
and   <YourDateColumn> < dateadd(day, +1, @dateend)

go

exec anlookup '2008-07-17', '2008-07-17', 'california'

Be One with the Optimizer
TG

---

quote:

---

Originally posted by fuzzyip

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by fuzzyip

I have an easy stored procedure

ALTER procedure [dbo].[anlookup]
@datebegin char(20),
@dateend char(20),
@state varchar(30)
as begin

select *
from ANInventorymm
where max between '@datebegin 0:00:01 am' and '@dateend 23:59:59 pm' and city like '%@state%'

end


The max column in table ANInventorymm is in a datetime format where all the dates look like this, '7/17/2008 2:42:31 AM'

When I run the stored proc with something like this...

anlookup '7/17/2008', '7/17/2008', 'california'

i get,

Msg 241, Level 16, State 1, Procedure anlookup, Line 7
Conversion failed when converting datetime from character string.



Anyone have any insight on this?

---

no need to append time like that, just use

select *
from ANInventorymm
where max >@datebegin and max <dateadd(d,1,@dateend)
and city like '%'+@state+'%'

---

when I change it to that, the storedproc won't update sucessfully,

Msg 102, Level 15, State 1, Procedure anlookup, Line 15
Incorrect syntax near '%'.

---

quote:

---

Originally posted by elancaster

show us what you actually have.

you're sure you didn't just miss off the 'END' or something?

Em

---