Reformatting Parameters before using them in Sql query

Hello,
We use Reporting Services to do reports from a (rather old iSeries /
AS400) database. Date values are stored as numeric data in the format
YYYYMMDD, rather than with a proper "Date" type. This is quite common in
older host-based databases.
In one of our reports, the user should be able to select data by
entering date-from and date-to into parameters, before starting the report.
All that we are able to do right now is to let the user enter the data
in the original YYYYMMDD format, in order to be able to use it in a
parametrized query. What I want to do, is:
a) Let the user enter a date as a parameter in a proper Date format
b) Before executing the query, convert this parameter to YYYYMMDD number
format
I guess this conversion should be done in a custom assembly. However,
there doesn't seem to be a "plug" where I could attach this conversion
code. The only thing I found is to use Generic Query - but this would
really be last resort, because I think it is very ugly and not really
RAD, to lose all of the graphic helpers just because of the date
conversions.
Any help would be greatly appreciated.
Ursuse the following custom code:
Public Function ConvertToYYYYMMDD(pYourDateFormat as Date) as String
ConvertToYYYYMMDD = Format(pYourDateFormat, "yyyyMMdd")
End Function 'ConvertToYYYYMMDD
Then in your query you would use for your parameter portion:
code.ConvertToYYYYMMDD(Parameters!YourDateParameter.Value)
"Urs Eichmann" <xx@.yy.ch> wrote in message
news:eRjnk4%23nEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hello,
> We use Reporting Services to do reports from a (rather old iSeries /
> AS400) database. Date values are stored as numeric data in the format
> YYYYMMDD, rather than with a proper "Date" type. This is quite common in
> older host-based databases.
> In one of our reports, the user should be able to select data by
> entering date-from and date-to into parameters, before starting the
report.
> All that we are able to do right now is to let the user enter the data
> in the original YYYYMMDD format, in order to be able to use it in a
> parametrized query. What I want to do, is:
> a) Let the user enter a date as a parameter in a proper Date format
> b) Before executing the query, convert this parameter to YYYYMMDD number
> format
> I guess this conversion should be done in a custom assembly. However,
> there doesn't seem to be a "plug" where I could attach this conversion
> code. The only thing I found is to use Generic Query - but this would
> really be last resort, because I think it is very ugly and not really
> RAD, to lose all of the graphic helpers just because of the date
> conversions.
> Any help would be greatly appreciated.
> Urs|||Thanks mike, but AFAIK I can only insert "code.convertto..." into my
query if I don't use the Graphic Query designer and instead use the
Generic Query Designer, which I don't want to (see my first message).
Urs
mike wrote:
> use the following custom code:
> Public Function ConvertToYYYYMMDD(pYourDateFormat as Date) as String
> ConvertToYYYYMMDD = Format(pYourDateFormat, "yyyyMMdd")
> End Function 'ConvertToYYYYMMDD
> Then in your query you would use for your parameter portion:
> code.ConvertToYYYYMMDD(Parameters!YourDateParameter.Value)
>
> "Urs Eichmann" <xx@.yy.ch> wrote in message
> news:eRjnk4%23nEHA.324@.TK2MSFTNGP11.phx.gbl...
>>Hello,
>>We use Reporting Services to do reports from a (rather old iSeries /
>>AS400) database. Date values are stored as numeric data in the format
>>YYYYMMDD, rather than with a proper "Date" type. This is quite common in
>>older host-based databases.
>>In one of our reports, the user should be able to select data by
>>entering date-from and date-to into parameters, before starting the
> report.
>>All that we are able to do right now is to let the user enter the data
>>in the original YYYYMMDD format, in order to be able to use it in a
>>parametrized query. What I want to do, is:
>>a) Let the user enter a date as a parameter in a proper Date format
>>b) Before executing the query, convert this parameter to YYYYMMDD number
>>format
>>I guess this conversion should be done in a custom assembly. However,
>>there doesn't seem to be a "plug" where I could attach this conversion
>>code. The only thing I found is to use Generic Query - but this would
>>really be last resort, because I think it is very ugly and not really
>>RAD, to lose all of the graphic helpers just because of the date
>>conversions.
>>Any help would be greatly appreciated.
>>Urs
>
>|||Urs,
Did you ever find a work around. I am in the same boat.
Thanks, Eric
"Urs Eichmann" wrote:
> Thanks mike, but AFAIK I can only insert "code.convertto..." into my
> query if I don't use the Graphic Query designer and instead use the
> Generic Query Designer, which I don't want to (see my first message).
> Urs
>
> mike wrote:
> > use the following custom code:
> >
> > Public Function ConvertToYYYYMMDD(pYourDateFormat as Date) as String
> > ConvertToYYYYMMDD = Format(pYourDateFormat, "yyyyMMdd")
> > End Function 'ConvertToYYYYMMDD
> >
> > Then in your query you would use for your parameter portion:
> > code.ConvertToYYYYMMDD(Parameters!YourDateParameter.Value)
> >
> >
> >
> > "Urs Eichmann" <xx@.yy.ch> wrote in message
> > news:eRjnk4%23nEHA.324@.TK2MSFTNGP11.phx.gbl...
> >
> >>Hello,
> >>We use Reporting Services to do reports from a (rather old iSeries /
> >>AS400) database. Date values are stored as numeric data in the format
> >>YYYYMMDD, rather than with a proper "Date" type. This is quite common in
> >>older host-based databases.
> >>
> >>In one of our reports, the user should be able to select data by
> >>entering date-from and date-to into parameters, before starting the
> >
> > report.
> >
> >>All that we are able to do right now is to let the user enter the data
> >>in the original YYYYMMDD format, in order to be able to use it in a
> >>parametrized query. What I want to do, is:
> >>
> >>a) Let the user enter a date as a parameter in a proper Date format
> >>b) Before executing the query, convert this parameter to YYYYMMDD number
> >>format
> >>
> >>I guess this conversion should be done in a custom assembly. However,
> >>there doesn't seem to be a "plug" where I could attach this conversion
> >>code. The only thing I found is to use Generic Query - but this would
> >>really be last resort, because I think it is very ugly and not really
> >>RAD, to lose all of the graphic helpers just because of the date
> >>conversions.
> >>
> >>Any help would be greatly appreciated.
> >>
> >>Urs
> >
> >
> >
>sql

Reformatting numeric value to zero-filled string

I need to take a numeric field that I need converted to a zero-filled text
value, for example:
123456 convert to 000123456
I've looked at the CONVERT and CASE and haven't come up with a combination
that will perform this function.
Thanks!
Dougtry this as an example:
SELECT ISNULL(RIGHT('000' + CONVERT(VARCHAR, '123456'), 10), '')
yosh
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||select replace(str( 10,10),' ','0')
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Try,
declare @.num int
set @.num = 123456
select right(replicate('0', 9) + ltrim(@.num), 9)
go
"Leighton.d" wrote:

> I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Wouldn't it be nice if the Access FORMAT$ function were available.
Thanks for all of the input...
Doug|||As a good practice, try the formatting in the client side.
AMB
"Leighton.d" wrote:

> Wouldn't it be nice if the Access FORMAT$ function were available.
> Thanks for all of the input...
> Doug