Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 39 ยท Functions and Expressions

Conversion and Numeric Functions in SQL Server

Convert types safely with CAST, CONVERT and TRY_CAST, round and truncate numbers with ROUND, CEILING and FLOOR, avoid integer division and use RAND and ABS.

Values change type all the time: a string from a form becomes a number, a number becomes text for a report, a date becomes a formatted label. SQL Server converts some of these automatically and demands you be explicit for the rest. This lesson covers explicit conversion with CAST, CONVERT, TRY_CAST, TRY_CONVERT and PARSE, then the numeric functions you need for rounding, math and random numbers, and the integer division trap.

CAST and CONVERT

CAST(expression AS type) is standard SQL and works everywhere. CONVERT(type, expression [, style]) is T-SQL only; its advantage is the optional style number that controls date and number formatting.

SQL
SELECT CAST('123' AS INT)                          AS as_int,
       CAST(19.99 AS INT)                          AS truncated,       -- 19, decimals cut off
       CAST(42 AS VARCHAR(10))                     AS as_text,
       CONVERT(VARCHAR(10), GETDATE(), 23)         AS iso_date,        -- 2025-03-14
       CONVERT(VARCHAR(10), GETDATE(), 103)        AS british_date,    -- 14/03/2025
       CONVERT(VARCHAR(10), GETDATE(), 101)        AS us_date;         -- 03/14/2025

Common date styles: 23 is yyyy-mm-dd, 120 is yyyy-mm-dd hh:mi:ss, 126 is full ISO 8601, 101 and 103 are US and British. Casting a decimal to an integer truncates rather than rounds. When you convert to VARCHAR always give a length; the default is 30 in CAST and 1 in a variable declaration, which silently chops text.

SQL CAST in action ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Safe conversion: TRY_CAST, TRY_CONVERT, TRY_PARSE

A failed conversion aborts the whole statement. The TRY_ versions (2012+) return NULL instead, which is perfect for cleaning imported data:

SQL
SELECT TRY_CAST('123' AS INT)        AS ok,            -- 123
       TRY_CAST('12x' AS INT)        AS bad,           -- NULL
       TRY_CONVERT(DATE, '2025-02-30') AS invalid_date; -- NULL

Find the problem rows with WHERE TRY_CAST(col AS INT) IS NULL AND col IS NOT NULL. PARSE and TRY_PARSE accept a culture ('de-DE') but use the .NET runtime and are slow, so avoid them on big tables. An old function, ISNUMERIC, is unreliable (it accepts '$' and '1e5'), so prefer TRY_CAST.

The editor's engine does not raise errors on bad casts, so the stand-in finds unconvertible values another way:

SQL Stand-in: find values that are not whole numbers

Press Run (or Ctrl + Enter) to see the result here.

Rounding functions

Function Behavior
ROUND(x, n) rounds to n decimals; negative n rounds the left side (ROUND(1234, -2) is 1200)
ROUND(x, n, 1) truncates instead of rounding
CEILING(x) smallest integer greater than or equal to x
FLOOR(x) largest integer less than or equal to x
ABS(x), SIGN(x) absolute value, sign (-1, 0, 1)

ROUND uses "half away from zero": ROUND(2.5, 0) is 3 and ROUND(-2.5, 0) is -3. The result keeps the type of the input, so ROUND(2.5, 0) of a DECIMAL stays a DECIMAL.

Other math functions

POWER(x, y), SQRT(x), SQUARE(x), EXP(x), LOG(x) (natural), LOG10(x), PI(), trigonometry (SIN, COS, ...) and x % y for the remainder. Newer versions add GREATEST() and LEAST() (2022 compatibility level 160), which return the largest and smallest of several values; they replace awkward CASE expressions.

SQL Rounding and math ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The integer division trap

When both operands are integers, / performs integer division and drops the fraction. This is the most common numeric bug in T-SQL reports:

SQL
SELECT 7 / 2            AS wrong,       -- 3
       7 / 2.0          AS right1,      -- 3.500000
       CAST(7 AS DECIMAL(5,2)) / 2 AS right2;  -- 3.500000

Percentages are the usual victim: COUNT(*) / total * 100 yields 0 for any fraction below 1. Multiply first by 100.0, or cast one side to DECIMAL.

Dividing by zero raises error 8134. Prevent it with NULLIF: a / NULLIF(b, 0) returns NULL when b is zero.

SQL Percentages without the integer trap ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Precision and scale of results

SQL Server decides the result type of arithmetic on DECIMAL values from the operands' precision and scale. Multiplying DECIMAL(10,2) by DECIMAL(10,2) gives DECIMAL(21,4), and very large results can lose scale digits or overflow. When exactness matters, cast the final result explicitly, CAST(a * b AS DECIMAL(12,2)), and make sure intermediate values are not rounded early.

Random numbers

RAND() returns a float between 0 and 1, but the same value for every row of a query because it is evaluated once. For different values per row use ABS(CHECKSUM(NEWID())) % 100. NEWID() creates a random GUID and is also a common way to shuffle rows: ORDER BY NEWID(). Passing a seed (RAND(42)) makes a call repeatable.

Prefer TRY_CAST when importing data from CSV files or user input; a single bad value then becomes a NULL you can inspect instead of an error that aborts the whole load.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does CAST(19.99 AS INT) return?

    Converting a decimal to an integer truncates the fraction. Use ROUND first if you want rounding.
  2. 2. What does TRY_CAST('12x' AS INT) return?

    TRY_CAST returns NULL when the conversion fails instead of raising an error.
  3. 3. What is 7 / 2 in T-SQL when both are integer literals?

    Integer division discards the remainder. Use 7 / 2.0 or cast one side to DECIMAL.
  4. 4. How do you avoid a divide-by-zero error in a / b?

    NULLIF(b, 0) returns NULL when b is 0, so the division yields NULL rather than an error.

Summary

  • CAST is standard; CONVERT adds style codes for dates and numbers; the TRY_ versions return NULL on failure.
  • Decimal-to-integer conversion truncates; ROUND, CEILING and FLOOR give control.
  • Integer division drops fractions: multiply by 100.0 or cast to DECIMAL.
  • Use NULLIF to prevent division by zero.
  • Per-row random numbers need NEWID(), not RAND().

Next: Conditional Expressions: CASE, IIF, COALESCE and ISNULL