Thursday, December 22, 2011

Converting CLng in MS Access SQL to T-SQL (SQL Server)

The problem I need to solve is how do I reproduce the exact functionality of the CLng (function built-in to MS Access SQL) in MS SQL Sever. The answer is not so difficult, but knowing what the question to ask was not so obvious.

After much frustration I finally realized that how CLng in MS Access SQL works is actually quite unintuitive. My situation can be explained quite simply. I have a query in MS Access that I need to convert to run completely on SQL Server. In parts of the MS Access queries the CLng function is used. CLng is similar to Round() in SQL Server, but is not the same particularly when it comes to values that end in .5. Initially I thought it would just truncate the decimals of a double. For example, I thought CLng(.5) should be 1, but MS Access says it is 0. Interestingly, CLng(3.5) is 4, not 3 and CLng(2.5) is 2, not 3. This behavior is very inconsistent to me, but I need to reproduce it on SQL Server using T-SQL so that I can compare the queries.

After some reading about issues related to rounding in general on Wikipedia I found out that there are many ways to round. After reading a technical article from Microsoft I found out that indeed VBA (and appears to be MS Access SQL) do not round the same as MS SQL Server. From this I learned the key to the solution; the CLng function in MS Access implements rounding using the Banker’s Rounding method described in both of the above sources. I did find an implementation of Banker’s Rounding once I knew what to search for. I found a good solution here. I have copy and pasted the solution below.

Here is that function you can execute on SQL Server that will do the same thing as Clng on MS Access SQL because it is an implementation of Banker’s Rounding.


create function MSAccessClng(@x as float)
returns int
as
BEGIN
Declare @DecimalPlaces as tinyint
Set @DecimalPlaces = 0

set @x = @x * power(10, @DecimalPlaces)

return
  case when @x = floor(@x) then @x
  else
    case sign(ceiling(@x) - 2*@x + floor(@x))
    when 1 then floor(@x)
    when -1 then ceiling(@x)
    else 2*round(@x/2,0) end
  end / power(10, @DecimalPlaces)
END

Now you have a function you can use on SQL Server just like you have on MS Access. Keep in mind this is a Scalar-valued function and this means there is a performance penalty for using it, but if you need this functionality you need the functionality.

Below are different examples of what I used to test that the functionality is the same on MS Access and MS SQL Server (T-SQL). Notice, the ones that say SAME means that CLng() has the same functionality as cast(). The ones that say DIFFERENCE means that the special logic was needed to implement differences. You can also see the usage of the function.

-- SAME
select clng(-3.3) -- -3
select cast(-3.3 as int) -- -3
select Round(-3.3, 0) -- 3
select dbo.MSAccessClng(-3.3) -- -3

-- SAME
select clng(-0.3) -- 0
select cast(-0.3 as int) -- 0
select round(-.3,0) -- 0
select dbo.MSAccessClng(-0.3) -- 0

-- DIFFERENCE
select clng(-3.5) -- -4
select cast(-3.5 as int) -- -3
select Round(-3.5,0) -- -4
select dbo.MSAccessClng(-3.5) -- -4

-- SAME
select clng(-0.5) -- 0
select cast(-0.5 as int) -- 0
select round(-0.5, 0) -- error
select dbo.MSAccessClng(-0.5) -- -0

-- DIFFERENCE
select clng(-0.51) -- -1
select cast(-0.51 as int) -- 0
select round(-0.51, 0) -- error
select dbo.MSAccessClng(-0.51) -- -1

-- DIFFERENCE
select clng(-0.8) -- -1
select cast(-0.8 as int) -- 0
select round(-0.8, 0) -- error
select dbo.MSAccessClng(-0.8) -- -1

-- SAME
select clng(-1) -- -1
select cast(-1 as int) -- -1
select round(-1, 0) -- -1
select dbo.MSAccessClng(-1) -- -1

-- SAME
select clng(-100000) -- -10000
select cast(-100000 as int) -- -10000
select round(-100000, 0) -- -10000
select dbo.MSAccessClng(-100000) -- -10000


-- SAME
select clng(3.3) -- 3
select cast(3.3 as int) -- 3
select Round(3.3, 0) -- 3
select dbo.MSAccessClng(3.3) -- 3

-- SAME
select clng(0.3) -- 0
select cast(0.3 as int) -- 0
select round(0.3,0) -- 0
select dbo.MSAccessClng(0.3) -- 0

-- DIFFERENCE
select clng(3.5) -- 4
select cast(3.5 as int) -- 3
select Round(3.5,0) -- 4
select dbo.MSAccessClng(3.5) -- 4

-- SAME
select clng(0.5) -- 0
select cast(0.5 as int) -- 0
select round(0.5, 0) -- error
select dbo.MSAccessClng(0.5) -- 0

-- DIFFERENCE
select clng(0.51) -- 1
select cast(0.51 as int) -- 0
select round(0.51, 0) -- error
select dbo.MSAccessClng(0.51) -- 1

-- DIFFERENCE
select clng(0.8) -- 1
select cast(0.8 as int) -- 0
select round(0.8, 0) -- error
select dbo.MSAccessClng(0.8) -- 1

-- SAME
select clng(1) -- 1
select cast(1 as int) -- 1
select round(1, 0) -- 1
select dbo.MSAccessClng(1) – 1

-- DIFFERENCE
select clng(34.5) -- 34
select cast(34.5 as int) -- 34
select Round(34.5,0) -- 35
select dbo.MSAccessClng(34.5) -- 4

-- DIFFERENCE
select clng(0) -- 0
select cast(0 as int) -- 0
select round(0, 0) -- 0
select dbo.MSAccessClng(0) – 0

-- SAME
select clng(100000) -- 10000
select cast(100000 as int) -- 10000
select round(100000, 0) -- 10000
select dbo.MSAccessClng(100000) -- 10000

2 comments:

Namrata Stellar said...

Hi Brent,

Hope you are doing well.

I hope my last email reached you well. I just wanted to reach you that if you have any queries before joining Stellar Affiliate Program.

We are industry’s leading Data Recovery Software makers with more than 4,000 affiliates associated with us, who are doing remarkably well. We take the pleasure to invite you to join our affiliate program and be a part of industries best-selling products like Data Recovery, Photo Recovery, and Email Recovery software.

For new affiliate, click on below link to register and get started with us. For any queries please feel free to contact me and I will be happy to assist you.

Register at any one network of your choice to start with us:
1. oneNetworkDirect: https://aff.onenetworkdirect.com/affiliate_signup.html?program_id=183
2. Avangate: https://www.avangate.com/affiliates/sign-up.php?merchant=STELLARI
3. ShareASale: http://www.shareasale.com/shareasale.cfm?merchantID=59114

Look forward for a long term business relationship with you. Have a great day ahead.

Regards
Nishant Kumar
Associate- Affiliate Marketing
Stellar Data Recovery
D-18, Infocity II, Sector 33 , Gurgaon (India)
Web : www.stellarinfo.com | Email : partnerenquiry@stellarinfo.com
M : +91 8447706889 | Skype : Affiliates.stellar

user5 srsoftwaresolutions said...

Did you know that BY 2020 the Affiliate
Marketing Industry is expected to be
worth $6.8 BILLION a YEAR in the US
alone.

Industry-leaders like ADWORDS PAY OUT
MILLIONS EVERY YEAR to their Affiliates.

As an Affiliate, your job is to find
people who want to buy a product on
marketplace.

When those people buy you get paid
commission.

It’s that simple!

Go to this private page right now to
see exactly how simple it can be:

http://five-minute-profit-sites.net?547456
You DON’T have to develop or supply
the products.

Many Product Owners will
pay you 75% COMMISSION!

That’s INSANELY HIGH!

OK so you might think that making an
income from Affiliate Marketing is easy.

Maybe you could become a millionaire
within a month?

Yeah right!

Anyone who promises you that is a
shameless scammer.

I know what it feels like to FAIL.

I know how much it hurts.

IT’S NOT YOUR FAULT.

To make consistent, daily income
from Affiliate Marketing..

..you need the right tools.

And you need the secret shortcuts.

After 9 years of experience as
7-FIGURE Affiliates..

..these guys have the tools which
ACTUALLY WORK. And they know the SECRET SHORTCUTS.

This private page will shock you: http://five-minute-profit-sites.net?547456