Converting a decimal

I need to convert a value 5.300000000 to 0005300. Basically I want to remove the decimal point, pad the out put with a couple of zeros and return a value that is 7 bytes long. I may need to convert something like 10.300000000 to be 0010300.
Anyone have any thoughts?
Thanks.DECLARE @.x decimal(5,2)
SELECT @.x = 5.3
SELECT @.x, RIGHT(REPLICATE('0',7)+REPLACE(CONVERT(varchar(7), @.x * 10),'.',''),7)|||Hi,
Here is a little code that should do it for you.
Granted it is ugly but it works

declare @.d decimal (16,9)
set @.d = 9.3000

print Replicate('0', 7 - len ( replace( Left(@.d,Charindex('.',Cast(@.d as Varchar))+3),'.','')))+ replace(Left(@.d,Charindex('.',Cast(@.d as varchar))+3),'.','')

Hope that Helps

Tal McMahon|||I have a function for zero-padding, which cleans things up a bit in my queries:

CREATE FUNCTION [dbo].[fn_zero_pad]
(@.string_data VARCHAR(100),
@.new_length INT)
RETURNS VARCHAR(100) AS
BEGIN

RETURN REPLICATE('0', @.new_length - LEN(@.string_data)) + @.string_data

END

Convert varbinary(16) to nvarchar

Hi,

Basically. I need to convert a varbinary(16) to a varchar in a sorted procedure (to update a table). [in query analyser key looks like 0x00000000000000000001]

Background:
I'm having to output replication commands from the distributor DB and want to store the sequence I'm upto to not have to repeat the same commands on the next export.

The stored proc for performing the export accepts an nvarchar(22) for the min key A(and this is how I've stored it). The records are then exported with varbinary(16) datatyped keys

I need to then store this key back but cannot seem to convert it.

Any thoughts are much appreciated.
Gaz

I've run into similar issues.

Try converting to a BigInt and storing that way. If the datatype is VarBin(16) and you shove in a value of 16, it will convert it. Usually, if an external application is expected a binary type, and you feed it 16 (without the 0x0 in the front) it will do it's own conversion.

So, you have varbin(16) type, value: 0x00000000000000000001

convert Hex value to Bigint (which, in this case would be = 1)

convert the bigint to a string and store the string (in this case "1"). Later when your app gets the string "1", it might just convert it appropriately. In any event, it's pretty easy to test.

Code Snippet

insert into MyTable (MyStringField)

select convert(varchar(32),convert(bigint,<MyHexValue>))

Convert Number to Hours and Minutes format

Hi

I am new to Crystal Reports 10 and I hope that somebody can help me with this.

Basically I am trying to convert a number to hours and minutes format.

What the guys here do is enter the time spent doing a particular job into Heat. This is not in time format, but simply a number. This can be 20, or 30 or whatever. To Heat, all it is is a number but we all know it means minutes.
The field is known as {Calllog.TimeSpent}

I then run a report that displays all calls logged for a particular group with the sum of {Calllog.TimeSpent} at the end to give the Total Time Spent on that group. However this number ends up being something like 2545. I could just divide by 60, but the rounding doesn't work the way I would like.

Is there a way that Crystal reports can take the sum of this field and format so that I get this kind of result:

The sum of the field is: 245
Formatted sum of field: 4 Hrs, 5 Mins

Thanks a lot
PhilHi Phil,

Hope this helps...

You have an object that returns a numeric value for minutes. You want to convert this into hours and minutes in a BusinessObjects report. For example, "243 Minutes" should become 4 hours and 3 minutes (4.03).

Resolution
*****CONFIGURATION******

BusinessObject version 4.1.x and 5.x

**********RESOLUTION******

To convert an object called <Original Value> that returns the value 243 to 4.03 follow the steps below:

1. Create a new variable called <Original Value /100> with the formula:

=<Original Value>/100

This will return the value 2.43 in our example.

2. Create a new variable called <Original Value /100 & 0.60> with the formula:

=<Original Value /100>/0.60.

This will return the value 4.05.

3. Create a new variable called <Truncated Div/0.60 (=Hours)> with the formula:

=Truncate(<Original Value /100 & 0.60> ,0).

This will return the value 4.00 and will be the Hour value at the end.

4. Create a variable called <Truncated Value * 0.60>. with the formula:

=<Truncated Div/0.60 (=Hours)>*0.60

This will return the value 2.40.

5. Create a variable called <Remainder (=Minutes)> with formula:

=<Original Value /100>-<Truncated Value * 0.60>

This will return the value 0.03.

6. Set up the entire calculation by creating a new variable called <Time Calculation> with the formula:

=<Truncated Div/0.60 (=Hours)>+<Remainder (=Minutes)>

This will give the correct conversion of 243 to 4.03 and will successfully convert any numeric Minutes value into the correct Hours/Minutes format.|||Seems like a lot of effort :)

numbervar a := 245;
numbervar hours := truncate(a / 60);
numbervar mins := a mod 60;
totext(hours, 0, '') + ' hours, ' + totext(mins, 0, '') + ' mins'|||Thank you very much guys, both methods worked perfectly when tweaked to suit my report.

I'm slowly starting to get my head around it all.

Thanks again!!!

Phil