SQL Server Built-in Functions: String, Date, and Math Functions You’ll Use Daily

Beyond aggregates, SQL Server ships a huge library of scalar functions — functions that take input and return one value per row, rather than collapsing many rows into one. Here are the ones you’ll actually reach for constantly, with the specific edge cases that catch people off guard.

String Functions

SELECT UPPER(name), LOWER(name), LEN(name) FROM dbo.Startup;
SELECT CONCAT(name, ' (', industry, ')') AS label FROM dbo.Startup;
SELECT SUBSTRING(name, 1, 4) FROM dbo.Startup;
SELECT TRIM('  padded  ') AS cleaned;
SELECT REPLACE(name, 'Data', 'Info') FROM dbo.Startup;
SELECT LEFT(name, 3), RIGHT(name, 3) FROM dbo.Startup;

Date Functions

SELECT GETDATE() AS right_now;              -- current date + time
SELECT SYSDATETIME() AS right_now_precise;  -- higher precision, preferred in new code
SELECT YEAR(GETDATE()) AS current_year;
SELECT DATEDIFF(YEAR, '2020-01-01', GETDATE()) AS years_since_2020;
SELECT DATEADD(MONTH, 6, GETDATE()) AS six_months_from_now;
SELECT DATENAME(WEEKDAY, GETDATE()) AS day_name;  -- e.g. 'Friday'

Math and Rounding Functions

SELECT ROUND(funding_usd / 1000000.0, 2) AS funding_millions FROM dbo.Startup;
SELECT CEILING(4.1) AS rounds_up;   -- 5
SELECT FLOOR(4.9) AS rounds_down;   -- 4
SELECT ABS(-42) AS absolute_value;  -- 42

The Integer Division Trap

SELECT 7 / 2 AS wrong_answer;      -- 3, NOT 3.5 — both operands are INT, result truncates
SELECT 7.0 / 2 AS correct_answer;  -- 3.5 — forcing one operand to a decimal type fixes it
SELECT CAST(7 AS DECIMAL(10,2)) / 2 AS also_correct;

NULL-Handling and Conversion Functions

SELECT ISNULL(NULL, 'fallback value') AS demo;               -- returns 'fallback value'
SELECT COALESCE(NULL, NULL, 'third option') AS demo2;        -- returns 'third option'
SELECT CAST(funding_usd AS BIGINT) AS funding_rounded FROM dbo.Startup;
SELECT TRY_CAST('not a number' AS INT) AS safe_conversion;   -- returns NULL, not an error

TRY_CAST (and its cousin TRY_CONVERT) return NULL instead of throwing an error when a conversion fails — invaluable when converting messy, real-world data where you can’t guarantee every value is well-formed.

Quick Reference