Convert $ to varchar decimal problem

I'm trying to convert a check amount to a fixed length string with
leading zeros and no decimal point. All is well, except for the pesky
decimal point. Here is what I have:
COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
(CONVERT(varchar(12),ckamt))
Thanks for any ideas on how to accomplish this.What exactly do you need? Could you give an example of the expected result?
E.g. I have 3.04, I want 003...
ML
http://milambda.blogspot.com/|||Multiply the number by 100 (or which ever multiple of 10 will remove the
decimal place), turn that number into an integer and then convert it into a
string so that 3.04 becomes 304.00 becomes 304 becomes 000304.
Ta,
M. E. Houston
<birdbyte@.gmail.com> wrote in message
news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...
> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.
>|||Take a look at this
declare @.d decimal(12,2)
select @.d =3.04
select @.d, right('000000000000' +
convert(varchar,replace(@.d,'.','')),12)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
birdbyte@.gmail.com wrote:
> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.|||Try,
declare @.m money
declare @.i int
set @.m = 12345.54
set @.i = 12
select replace(str(round(@.m, 0, 1), @.i, 0), ' ', '0')
go
AMB
"birdbyte@.gmail.com" wrote:

> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.
>|||Replicate to 13, and then REPLACE({your stuff below}, '.', '')
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<birdbyte@.gmail.com> wrote in message
news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...
> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.
>|||I don't think this this idea is quite right. If you REPLACE() the decimal in
the decimal value, it will round.
I think you need to REPLACE() the decimal after it is converted to a
varchar().
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1151514826.136274.129340@.x69g2000cwx.googlegroups.com...
> Take a look at this
> declare @.d decimal(12,2)
> select @.d =3.04
> select @.d, right('000000000000' +
> convert(varchar,replace(@.d,'.','')),12)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> birdbyte@.gmail.com wrote:
>|||Or convert the decimal to an int then convert to varchar. These two
methods are assuming you want to always round down.
On Wed, 28 Jun 2006 11:52:35 -0500, "M. E. Houston"
<m.e.houston@.gmail.com> wrote:

>Multiply the number by 100 (or which ever multiple of 10 will remove the
>decimal place), turn that number into an integer and then convert it into a
>string so that 3.04 becomes 304.00 becomes 304 becomes 000304.
>Ta,
>M. E. Houston
><birdbyte@.gmail.com> wrote in message
>news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...
>|||Great suggestion. Thanks.
Arnie Rowland wrote:
> Replicate to 13, and then REPLACE({your stuff below}, '.', '')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> <birdbyte@.gmail.com> wrote in message
> news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...

Conversion failed when converting datetime from character string

Hi,

I receive an Error Message: Conversion failed when converting datetime from character string when I try to run this.

Can someone point out what I'm doing wrong?

SELECT Principal,

SUM(CASE WHEN Recdate BETWEEN '=@.LYbegin' AND '=@.LYend' THEN

Amount ELSE 0 END) AS LY,

SUM(CASE WHEN Recdate BETWEEN '=@.TYbegin' AND '=@.TYend' THEN

Amount ELSE 0 END) AS TY

FROM dbo.Checks

GROUP BY Principal

If I execute the query with the dates it works fine:

SELECT Principal,

SUM(Case When RecDate BETWEEN '1-1-2005 00:00:00.000' AND '1-30-2005 00:00:00.000' THEN Amount else 0 end)AS LY,

SUM(Case When RecDate BETWEEN '2-1-2005 00:00:00.000' AND '2-28-2005 00:00:00.000' THEN Amount else 0 end)AS TY

FROM Checks

GROUP BY Principal

Thanks,

Terry McCullagh

I suppose you are trying to use a parameterized query statement in the RS query designer. You should use the following commandtext to have query parameters being detected and working:

SELECT Principal,
SUM(CASE WHEN Recdate BETWEEN @.LYbegin AND @.LYend THEN Amount ELSE 0 END) AS LY,
SUM(CASE WHEN Recdate BETWEEN @.TYbegin AND @.TYend THEN Amount ELSE 0 END) AS TY
FROM dbo.Checks
GROUP BY Principal

-- Robert

|||

Robert,

That works great.

Thank you,

Terry McCullagh

Controls pushed off to the right

This is pretty weird... any controls past the halfway horizontal point of the report get pushed into page 2. The leftmost control is not affected just those to the right of it. It's like there is an invisible tab after the leftmost control. Smaller (not leftmost controls) (not past the middle of the page) are also pushed way to the right (just not into a new page)

Is there some property (that I'm not aware of) that does this? It doesn't matter if I use textboxes or lines. I have not changed any of the default control properties. flow layout is still LTR.
In my current report I have questions and answers on separate lines...I can't place them next to each other or it screws up the pagination.

I start with a new report and it does not do this but as soon as I add 2-3 vertical pages (over 11' x 2) This starts happening.

Has anyone experienced this?

I have experienced this in trying to lay out textboxes next to each other. The one to the right ends up getting pushed much further to the right then I want it to. The I've found to stop this from happening is to put both of my text boxes in a rectangle and then size the rectangle to match the width of the page. Then I can place my text boxes where I want within the rectangle and they'll stay put, for the most part.

Controls pushed off to the right

This is pretty weird... any controls past the halfway horizontal point
of the report get pushed into page 2. The leftmost control is not
affected just those to the right of it. It's like there is an
invisible tab after the leftmost control. Smaller (not leftmost
controls) (not past the middle of the page) are also pushed way to the
right (just not into a new page)
Is there some property (that I'm not aware of) that does this? It
doesn't matter if I use textboxes or lines. I have not changed any of
the default control properties. flow layout is still LTR.
In my current report I have questions and answers on separate
lines...I can't place them next to each other or it screws up the
pagination.
I start with a new report and it does not do this but as soon as I add
2-3 vertical pages (over 11' x 2) This starts happening.
Has anyone experienced this?Select from the menu format->align and left/center/right. I think it should
be with alignment. because you said all the controls comes to left side of
the page.
Amarnath
"tom booster" wrote:
> This is pretty weird... any controls past the halfway horizontal point
> of the report get pushed into page 2. The leftmost control is not
> affected just those to the right of it. It's like there is an
> invisible tab after the leftmost control. Smaller (not leftmost
> controls) (not past the middle of the page) are also pushed way to the
> right (just not into a new page)
> Is there some property (that I'm not aware of) that does this? It
> doesn't matter if I use textboxes or lines. I have not changed any of
> the default control properties. flow layout is still LTR.
> In my current report I have questions and answers on separate
> lines...I can't place them next to each other or it screws up the
> pagination.
> I start with a new report and it does not do this but as soon as I add
> 2-3 vertical pages (over 11' x 2) This starts happening.
> Has anyone experienced this?
>

Control of Parameter Field Layout

Greetings

Is it possible to control how the Parameter fields are displayed on report at the point of selection? At the moment they are equally dropped on the form in two columns. I would like to place the Alpha Numerical ones at the top and then organise the date parameters underneath in some kind of logical order.

Regards

The parameter layout is not configurable. But you can order the parameters in your report to get two columns in a more logical order.

|||

Oh well that is a shame.

Thank you anyway

Regards

Control flow drawing bug

Minor issue here. But I just wanted to point it out.

I think this is how to reproduce it. If I disable a dataflow task and run the package, after it is complete, but yet still in the debugger, reenable the dataflow task. When I close the debugger, the dataflow task is still shaded as if it were disabled, but it will execute on the next run.

Close the package and re-open it and the work flow task is drawn correctly.

Thanks for the report, Jim. What build is this with? We've fixed one of these occurrences recently.

regards,
ash|||I am still on the April CTP.|||OK. I believe this has been fixed in the next CTP (CTP16). Please review when you get that, thanks!