Converting Date Data type in stored procedure

HI Experts...

I am using SQL SERVER 2005 standard edition

I have encountered a problem regarding converting date data type in stored procedure

As i was having problem taking date as input parameter in my stored procedure, so, then I changed to varchar (16) i.e.

CREATE PROCEDURE sp_CalendarCreate
@.StDate VARCHAR(16) ,
@.EDate VARCHAR(16),

then I am converting varchar to date with following code

DECLARE @.STARTDATE DATETIME
DECLARE @.ENDDATE DATETIME

SELECT @.STARTDATE = CAST(@.STDATE AS DATETIME)
SELECT @.ENDDATE = CAST(@.EDATE AS DATETIME)

When I try to execute the procedure with following code

execute sp_CalendarCreate @.stdate='12-1-06',@.edate='20-1-06'

but it gives me following error

Msg 242, Level 16, State 3, Procedure sp_CalendarCreate, Line 45
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Can any one tell me the solution of that problem (at ur earliest)

regards,

Anas

execute sp_CalendarCreate @.stdate='1-12-06',@.edate='1-20-06'

|||

Thanx for your reply....

yes i had sorted that out b4 u replied me... i.e. sql server uses date format mmddyy or yyyyddmm (american format)

and i was using UK/european standards

btw thanx very much for support

regards,

Anas

|||It is best to use ISO unseparated date format (YYYMMDD) or ISO 8601 (YYYY-MM-DDThh:mm:ss.nnn) datetime format for specifying values. This way you don't have to worry about DATEFORMAT or language settings of the server. Also, using varchar and converting to datetime is not a good and clean approach. Use the correct data type for the values so you can get the best benefit in terms of type checking, domain validations etc. Additionally, with your approach you will get into performance problems because parameter sniffing of the variables used in the SELECT statement will not work due to use of local variables and not parameters. See the whitepaper on compilation, caching in MSDN for more details.

Convert multivalued parameters to a string

I have read all the articles regarding this issue.
I would like to implent an "select all" function in the multivalued prompt.
The multivalued datavalues comes from a table. e.x.
select label,value from basetable
union
select 'All' as label,'%' as value
The user choice comes in the parameter @.BasePick
I have tried different SQL
1:
select id,data1,data2 from Trans t
inner join charlist_to_table(@.BasePick,Default) f on t.id = f.str
result: SQL gives an "comma error" when user pick several multivalue elements
2:
select id,data1,data2 from Trans t
where @.BasePick='%' or t.id in (@.BasePick)
result: SQL gives an "comma error" when user pick several multivalue elements
I guess that there are no way to convert an RS array to a string for use in
SQL. What is the best pracsis here.
--
Regards
Henrik JuelI presume you use RS2005.
the SP2 will provide the Select All option in a multivalue parameter.
to convert a multivalue to a string, use the Join code:
=Join(myparam.Value, ",")
returns a comma separated string.
But in your case, use 2 parameters in your query.
The first one use the BasePick multivalue parameter
the second one will use a formula (RS formula) like:
select id,data1,data2 from Trans t
where @.AllPick = '%' or t.id in (@.BasePick)
@.AllPick will receive the result of this formula:
=myparam.Value(0)
(to return only the first value from the multivalue)
"Henrik Juel" <HenrikJuel@.newsgroup.nospam> wrote in message
news:3393164A-DFEC-45F9-9E1F-435C0892DA64@.microsoft.com...
>I have read all the articles regarding this issue.
> I would like to implent an "select all" function in the multivalued
> prompt.
> The multivalued datavalues comes from a table. e.x.
> select label,value from basetable
> union
> select 'All' as label,'%' as value
> The user choice comes in the parameter @.BasePick
> I have tried different SQL
> 1:
> select id,data1,data2 from Trans t
> inner join charlist_to_table(@.BasePick,Default) f on t.id = f.str
> result: SQL gives an "comma error" when user pick several multivalue
> elements
> 2:
> select id,data1,data2 from Trans t
> where @.BasePick='%' or t.id in (@.BasePick)
> result: SQL gives an "comma error" when user pick several multivalue
> elements
> I guess that there are no way to convert an RS array to a string for use
> in
> SQL. What is the best pracsis here.
> --
> Regards
> Henrik Juel|||Hi all,
I having same problem with these multi value params. I am using that
"CharList" function in my SSRS 2005 report SP s.I have number of
Cascading multi value parameters in my reprots. And also these mv
params taking long time to refresh. If user selects 'All' giving an
"comma error". Can any body tell me which one is the better methodology
in performance wise. If you people solved this problem please let me
know..
Thanks,
Munny.
Jeje wrote:
> I presume you use RS2005.
> the SP2 will provide the Select All option in a multivalue parameter.
> to convert a multivalue to a string, use the Join code:
> =Join(myparam.Value, ",")
> returns a comma separated string.
> But in your case, use 2 parameters in your query.
> The first one use the BasePick multivalue parameter
> the second one will use a formula (RS formula) like:
> select id,data1,data2 from Trans t
> where @.AllPick = '%' or t.id in (@.BasePick)
> @.AllPick will receive the result of this formula:
> =myparam.Value(0)
> (to return only the first value from the multivalue)
>
> "Henrik Juel" <HenrikJuel@.newsgroup.nospam> wrote in message
> news:3393164A-DFEC-45F9-9E1F-435C0892DA64@.microsoft.com...
> >I have read all the articles regarding this issue.
> >
> > I would like to implent an "select all" function in the multivalued
> > prompt.
> > The multivalued datavalues comes from a table. e.x.
> >
> > select label,value from basetable
> > union
> > select 'All' as label,'%' as value
> >
> > The user choice comes in the parameter @.BasePick
> >
> > I have tried different SQL
> >
> > 1:
> > select id,data1,data2 from Trans t
> > inner join charlist_to_table(@.BasePick,Default) f on t.id = f.str
> >
> > result: SQL gives an "comma error" when user pick several multivalue
> > elements
> >
> > 2:
> > select id,data1,data2 from Trans t
> > where @.BasePick='%' or t.id in (@.BasePick)
> >
> > result: SQL gives an "comma error" when user pick several multivalue
> > elements
> >
> > I guess that there are no way to convert an RS array to a string for use
> > in
> > SQL. What is the best pracsis here.
> >
> > --
> > Regards
> > Henrik Juel|||Thx for a good reply.
Especially using two parameters was useful. I use an interal RS variable
@.Allpick that default is set to BasePick.value(0). Then I can use the
where @.AllPick = '%' or t.id in (@.BasePick)
Regards
Henrik Juel
"Jeje" wrote:
> I presume you use RS2005.
> the SP2 will provide the Select All option in a multivalue parameter.
> to convert a multivalue to a string, use the Join code:
> =Join(myparam.Value, ",")
> returns a comma separated string.
> But in your case, use 2 parameters in your query.
> The first one use the BasePick multivalue parameter
> the second one will use a formula (RS formula) like:
> select id,data1,data2 from Trans t
> where @.AllPick = '%' or t.id in (@.BasePick)
> @.AllPick will receive the result of this formula:
> =myparam.Value(0)
> (to return only the first value from the multivalue)
>
> "Henrik Juel" <HenrikJuel@.newsgroup.nospam> wrote in message
> news:3393164A-DFEC-45F9-9E1F-435C0892DA64@.microsoft.com...
> >I have read all the articles regarding this issue.
> >
> > I would like to implent an "select all" function in the multivalued
> > prompt.
> > The multivalued datavalues comes from a table. e.x.
> >
> > select label,value from basetable
> > union
> > select 'All' as label,'%' as value
> >
> > The user choice comes in the parameter @.BasePick
> >
> > I have tried different SQL
> >
> > 1:
> > select id,data1,data2 from Trans t
> > inner join charlist_to_table(@.BasePick,Default) f on t.id = f.str
> >
> > result: SQL gives an "comma error" when user pick several multivalue
> > elements
> >
> > 2:
> > select id,data1,data2 from Trans t
> > where @.BasePick='%' or t.id in (@.BasePick)
> >
> > result: SQL gives an "comma error" when user pick several multivalue
> > elements
> >
> > I guess that there are no way to convert an RS array to a string for use
> > in
> > SQL. What is the best pracsis here.
> >
> > --
> > Regards
> > Henrik Juel
>|||in fact, an internal variable is not required.
you can just map the @.AllPick query parameter to a formula instead-of
creating a new report parameter.
"Henrik Juel" <HenrikJuel@.newsgroup.nospam> wrote in message
news:D42F6BDE-08C5-466F-9D79-6B9973B62E56@.microsoft.com...
> Thx for a good reply.
> Especially using two parameters was useful. I use an interal RS variable
> @.Allpick that default is set to BasePick.value(0). Then I can use the
> where @.AllPick = '%' or t.id in (@.BasePick)
>
> --
> Regards
> Henrik Juel
>
> "Jeje" wrote:
>> I presume you use RS2005.
>> the SP2 will provide the Select All option in a multivalue parameter.
>> to convert a multivalue to a string, use the Join code:
>> =Join(myparam.Value, ",")
>> returns a comma separated string.
>> But in your case, use 2 parameters in your query.
>> The first one use the BasePick multivalue parameter
>> the second one will use a formula (RS formula) like:
>> select id,data1,data2 from Trans t
>> where @.AllPick = '%' or t.id in (@.BasePick)
>> @.AllPick will receive the result of this formula:
>> =myparam.Value(0)
>> (to return only the first value from the multivalue)
>>
>> "Henrik Juel" <HenrikJuel@.newsgroup.nospam> wrote in message
>> news:3393164A-DFEC-45F9-9E1F-435C0892DA64@.microsoft.com...
>> >I have read all the articles regarding this issue.
>> >
>> > I would like to implent an "select all" function in the multivalued
>> > prompt.
>> > The multivalued datavalues comes from a table. e.x.
>> >
>> > select label,value from basetable
>> > union
>> > select 'All' as label,'%' as value
>> >
>> > The user choice comes in the parameter @.BasePick
>> >
>> > I have tried different SQL
>> >
>> > 1:
>> > select id,data1,data2 from Trans t
>> > inner join charlist_to_table(@.BasePick,Default) f on t.id = f.str
>> >
>> > result: SQL gives an "comma error" when user pick several multivalue
>> > elements
>> >
>> > 2:
>> > select id,data1,data2 from Trans t
>> > where @.BasePick='%' or t.id in (@.BasePick)
>> >
>> > result: SQL gives an "comma error" when user pick several multivalue
>> > elements
>> >
>> > I guess that there are no way to convert an RS array to a string for
>> > use
>> > in
>> > SQL. What is the best pracsis here.
>> >
>> > --
>> > Regards
>> > Henrik Juel