Problem
One needs to be careful when using the T-SQL trigonometric functions in SQL Server to prevent erroneous output. The results may not always be what they should be. Read this tip and review the examples to learn why.
Solution
In this tip we will start with what works and then we will review the pitfalls that one might encounter when using SQL Server T-SQL trigonometric functions. All examples shown will use the cosine function and use values in both degrees and radians. These first two queries return the correct values for cosine.
select round(cos(0*pi()/180.0),3) as cosineOf0,
round(cos(90.0*pi()/180.0),3) as cosineOf90,
round(cos(180.0*pi()/180.0),3) as cosineOf180,
round(cos(270.0*pi()/180.0),3) as cosineOf270,
round(cos(360.0*pi()/180.0),3) as cosineOf360
select round(cos(radians(0.0)),3) as cosineOf0,
round(cos(radians(90.0)),3) as cosineOf90,
round(cos(radians(180.0)),3) as cosineOf180,
round(cos(radians(270.0)),3) as cosineOf270,
round(cos(radians(360.0)),3) as cosineOf360
The next two queries demonstrate what happens if you do not round your results. The results for the angle at pi*n radians is correct, but the results at pi*n/2 radians are not correct. The values approach zero, but never equal zero.
select cos(0*pi()/180.0) as cosineOf0,
cos(90.0*pi()/180.0) as cosineOf90,
cos(180.0*pi()/180.0) as cosineOf180,
cos(270.0*pi()/180.0) as cosineOf270,
cos(360.0*pi()/180.0) as cosineOf360
select cos(radians(0.0)) as cosineOf0,
cos(radians(90.0)) as cosineOf90,
cos(radians(180.0)) as cosineOf180,
cos(radians(270.0)) as cosineOf270,
cos(radians(360.0)) as cosineOf360
The next two queries show what happens when you use integers for the value of the angle in degrees. The conversion from degrees to radians in the first query below is successful, but in the second query the results are not correct.
select round(cos(0*pi()/180),3) as cosineOf0,
round(cos(90*pi()/180),3) as cosineOf90,
round(cos(180*pi()/180),3) as cosineOf180,
round(cos(270*pi()/180),3) as cosineOf270,
round(cos(360*pi()/180),3) as cosineOf360
select round(cos(radians(0)),3) as cosineOf0,
round(cos(radians(90)),3) as cosineOf90,
round(cos(radians(180)),3) as cosineOf180,
round(cos(radians(270)),3) as cosineOf270,
round(cos(radians(360)),3) as cosineOf360
If we look closely at the radians function in the next two queries, we can find the source of the error.
select radians(0.0) as Radians0,
radians(90.0) as Radians90,
radians(180.0) as Radians180,
radians(270.0) as Radians270,
radians(360.0) as Radians360
select radians(0) as Radians0,
radians(90) as Radians90,
radians(180) as Radians180,
radians(270) as Radians270,
radians(360) as Radians360
When passing an integer to the radians function, the results returned are an integer which is the floor (rounded down) value. When passing a floating point value to the radians function, the results returned are the correct floating point value.
Next Steps
- When using trigonometric functions in T-SQL, make sure to test for cases where the angle is at 0, 90, 180, 270 and 360 degrees. This is the same as 0, pi/2, pi, 3*pi/2 and 2*pi radians.
- Check out these tips on other T-SQL functions on MSSQLTips.com.
Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida. He received his Ph.D. in Integrated Computing and M.S. in Instrumental Sciences from the University of Arkansas at Little Rock. He received a B.A. in Physics from Hendrix College. Before joining UWF, he worked as a data warehouse developer for Northrop Grumman Information Systems and prior to that as a database application developer for Acxiom and Euronet. Dr. Snider’s teaching and research interests include data mining, data warehousing, information visualization, and software development.
