SQL Server’s built-in function library covers string manipulation, date arithmetic, type conversion, and conditional logic. Knowing the right function for each task avoids pulling data into application code for processing. Returning a formatted date string, extracting the year from a timestamp, replacing characters in a slug, or safely converting a user input value — all done in T-SQL, reducing round-trips and keeping transformation logic close to the data.
String, Date and Conversion Functions
-- ── String functions ──────────────────────────────────────────────────────
SELECT
LEN(Title) AS TitleLength,
LEFT(Title, 50) AS TitlePreview,
RIGHT(Slug, 10) AS SlugSuffix,
SUBSTRING(Body, 1, 200) AS BodyExcerpt,
CHARINDEX('.NET', Title) AS DotNetPosition, -- 0 if not found
REPLACE(Title, '''', '''''') AS EscapedTitle, -- double single quotes
UPPER(Status) AS StatusUpper,
LOWER(Slug) AS SlugLower,
TRIM(Body) AS TrimmedBody, -- removes leading/trailing spaces
CONCAT(u.DisplayName, ' - ', p.Title) AS FullTitle, -- null-safe concatenation
FORMAT(ViewCount, 'N0') AS FormattedViews -- "1,234"
FROM dbo.Posts p
JOIN dbo.Users u ON u.Id = p.AuthorId;
-- ── Date functions ────────────────────────────────────────────────────────
SELECT
SYSUTCDATETIME() AS NowUtc,
GETUTCDATE() AS NowUtcLegacy, -- lower precision
DATEADD(DAY, 30, PublishedAt) AS ExpiresAt,
DATEADD(MONTH, -1, SYSUTCDATETIME()) AS OneMonthAgo,
DATEDIFF(DAY, PublishedAt, SYSUTCDATETIME()) AS DaysSincePublished,
DATEDIFF(HOUR, CreatedAt, UpdatedAt) AS HoursToUpdate,
DATEPART(YEAR, PublishedAt) AS PublishYear,
DATEPART(MONTH, PublishedAt) AS PublishMonth,
FORMAT(PublishedAt, 'yyyy-MM-dd') AS FormattedDate,
CONVERT(VARCHAR, PublishedAt, 23) AS IsoDate -- 'YYYY-MM-DD'
FROM dbo.Posts
WHERE IsPublished = 1;
-- ── Conversion functions ──────────────────────────────────────────────────
-- CAST — standard SQL; throws on conversion failure
SELECT CAST('42' AS INT) AS ParsedInt;
-- TRY_CAST — returns NULL on failure (safe for user input)
SELECT TRY_CAST('not-a-number' AS INT) AS SafeParse; -- returns NULL, not error
SELECT TRY_CAST('2024-01-15' AS DATE) AS SafeDate;
-- CONVERT — SQL Server specific; supports style codes for dates
SELECT CONVERT(VARCHAR, SYSUTCDATETIME(), 120) AS ISO8601; -- '2024-07-15 14:30:00'
SELECT CONVERT(VARCHAR, SYSUTCDATETIME(), 23) AS DateOnly; -- '2024-07-15'
-- ── NULL handling functions ───────────────────────────────────────────────
SELECT
ISNULL(Excerpt, SUBSTRING(Body, 1, 200)) AS DisplayExcerpt, -- first non-null
COALESCE(Excerpt, Subtitle, LEFT(Body, 200)) AS BestExcerpt, -- first of many
NULLIF(Status, 'draft') AS NonDraftStatus -- returns NULL if Status = 'draft'
FROM dbo.Posts;
-- ── CASE expression — conditional logic ───────────────────────────────────
SELECT
Id, Title,
CASE Status
WHEN 'draft' THEN 'Not Started'
WHEN 'review' THEN 'In Review'
WHEN 'published' THEN 'Live'
WHEN 'archived' THEN 'Hidden'
ELSE 'Unknown'
END AS StatusLabel,
CASE
WHEN ViewCount > 10000 THEN 'Viral'
WHEN ViewCount > 1000 THEN 'Popular'
WHEN ViewCount > 100 THEN 'Normal'
ELSE 'Low Traffic'
END AS TrafficLevel
FROM dbo.Posts;Note: Prefer
TRY_CAST() and TRY_CONVERT() over CAST() and CONVERT() when working with user-provided data or data whose format cannot be guaranteed. CAST('abc' AS INT) throws an error that terminates the query; TRY_CAST('abc' AS INT) returns NULL, allowing the query to continue. In stored procedures and queries that process imported data or user input, always use the TRY variants to avoid runtime failures on unexpected values.Tip: Use
COALESCE() instead of ISNULL() when checking multiple columns or expressions for the first non-null value. COALESCE(a, b, c) returns the first non-null value among a, b, and c — equivalent to CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END. ISNULL(a, b) only handles two arguments. Both are null-safe, but COALESCE is the ANSI SQL standard and works in all databases; ISNULL is SQL Server-specific.Warning: Avoid using functions on indexed columns in WHERE clauses — they prevent index usage.
WHERE YEAR(PublishedAt) = 2024 forces a full scan even if there is an index on PublishedAt. Instead, use a range: WHERE PublishedAt >= '2024-01-01' AND PublishedAt < '2025-01-01' — this allows the index to be used for a range seek. Similarly, WHERE LOWER(Email) = 'user@example.com' prevents index use; store emails pre-lowercased or use a case-insensitive collation.Common Mistakes
Mistake 1 — Function on indexed column in WHERE (index not used, full scan)
❌ Wrong — WHERE YEAR(PublishedAt) = 2024; function wraps the column; index cannot be used.
✅ Correct — WHERE PublishedAt >= '2024-01-01' AND PublishedAt < '2025-01-01'.
Mistake 2 — CAST instead of TRY_CAST for user/external data (query fails on bad input)
❌ Wrong — CAST(userInput AS INT); non-numeric input terminates the query with an error.
✅ Correct — TRY_CAST(userInput AS INT); returns NULL for invalid values without crashing.