Trigger,

How do you use BCP from T-SQL without using xp_CmdShell which is normally off limits to all but those members of the SA role? 

Thgamble1,

Carl has the correct answer here... did it work for you or do you still have a question?

It would be real nice if you posted the function that work's so great... thanks.

Andrew, you're making a classic error... you are doing traditional rounding to 3 places before you are attempting the bankers rounding....

Just like the example of 3.445657545 being rounded to 3.45...

Carl wrote: "For 3.455, since the third decimal position value is 5 and the second decimal position is odd, this should truncate, not round. The expected results should be 3.45...

BWAAA-HAAAA-HAAAA-HAAA! Snooorrtt! OOOOOOOHHHHH-WEEEEEE! UNBELIEVABLE!!!!  WE DON' NEED NO STINKIN' LOOPS... WE DON' NEED NO STINKIN' UDF.  WE DON' NEED NO STINKIN' CALCULATIONS!!!!  SQL SERVER HAS A FUNCTION TO DO BANKERS...

As a DOS command, you can use OSQL -L

You could route that to a file using OSQL -L > somefilename

AND, dont' feel too alone on that MONEY v.s. DECIMAL thing... I just changed to the DECIMAL data type on my attempted code and ALL the answers became 3.45

Ryan,

Your code works great so long as the MONEY data type is used to hold the number and is probably appropriate for the Banker's Rounding problem because one must assume...

Ryan, You are correct.  It doesn't work correctly for 3.445657545.  It rounds to 3.44 instead of 3.45 as you stated.  Thanks for the catch...

An interesting mix of delight and pain... sort of like sweet-and-sour sauce with a generous dose of horse-radish... and I love horse-radish!

And thanks for all you do behind the scenes,...

Andrew... This also works... and, it's set based.... just substitute a column name for @Number in the formula...

-- Math.Round(3.454,2) -> 3.45

-- Math.Round(3.455,2) -> 3.46

-- Math.Round(3.445,2) -> 3.44

-- Math.Round(3.456,2)...

If you mean round to the nearest whole number, then simply converting to INT will do...

DECLARE @Amount1 MONEY

    SET @Amount1 = 7.49

DECLARE @Amount2 MONEY

    SET @Amount2 = 7.50

DECLARE...

You bet... Merry Christmas.

You are new at this...

Try this, Josh...

   UPDATE yourtable

      SET Name = RTRIM(SUBSTRING(Subject,1,CHARINDEX('#',Subject,1)-1)),

          Paid = RTRIM(SUBSTRING(Subject,CHARINDEX('#',Subject,1),

                                         CHARINDEX('$',Subject,1)-CHARINDEX('#',Subject,1))),

        Amount = RTRIM(SUBSTRING(Subject,CHARINDEX('$',Subject,1),

                                         CHARINDEX('/',Subject,1)-CHARINDEX('$',Subject,1))),

       CheckNo = RTRIM(SUBSTRING(Subject,CHARINDEX('/',Subject,1)+1,999))

     FROM yourtable

And, let's hope...