8. Working with Text and Dates

Core Idea: Text and dates have built-in database functions. You can trim whitespace, concatenate strings, or extract year, month, and day components from datetime fields.

Common Text Functions & Concepts

Calculating string length, case normalization (upper/lower case), substring slicing, and pattern matching inside strings.

Common Date Functions & Concepts

Retrieving current system timestamp, extracting calendar parts with YEAR(DateCol), and executing date range comparisons.

Practical Examples

Common real-world patterns: formatting customer full names and filtering records by purchase fiscal year.

Advanced Deep Dive ADVANCED

Collation: Governs character comparison, sorting, and sensitivity; mixing mismatched collations requires explicit COLLATE clauses and can hinder index seek performance.

NVARCHAR vs. VARCHAR: NVARCHAR stores UTF-16 Unicode characters; required for international text or special symbols (consumes 2 bytes per character).

Precision Date Types: Favor DATETIME2 for higher fractional-second precision; SMALLDATETIME truncates seconds; DATETIMEOFFSET captures explicit time zone offsets.

Function SARGability: Wrapping indexed columns inside functions in a WHERE clause (e.g. WHERE UPPER(Col) = 'VAL') breaks index seeks; use computed persisted columns or query normalized inputs.

ISO 8601 Parsing: Standardizing on the YYYY-MM-DDThh:mm:ss format eliminates cultural date ambiguity when converting strings to dates.

next