Bài 7: String và DateTime Functions

Time and Text Processing

1. String Functions

1.1 Basic String Functions

Function	Purpose	Example
`LEN()`	Length (without trailing spaces)	`LEN('Hello ')` → 5
`DATALENGTH()`	Bytes in string	`DATALENGTH(N'Việt Nam')` → 16
`LEFT()`	Left n characters	`LEFT('Hello', 2)` → 'He'
`RIGHT()`	Right n characters	`RIGHT('Hello', 2)` → 'lo'
`SUBSTRING()`	Extract portion	`SUBSTRING('Hello', 2, 3)` → 'ell'
`UPPER()`	Uppercase	`UPPER('hello')` → 'HELLO'
`LOWER()`	Lowercase	`LOWER('HELLO')` → 'hello'
`LTRIM()`	Remove leading spaces	`LTRIM(' Hello')` → 'Hello'
`RTRIM()`	Remove trailing spaces	`RTRIM('Hello ')` → 'Hello'
`TRIM()`	Remove both (SQL 2017+)	`TRIM(' Hello ')` → 'Hello'

1.2 String Functions với AdventureWorks

SQL

1-- Tên employee uppercase
2SELECT 
3    BusinessEntityID,
4    UPPER(FirstName) AS FirstName_Upper,
5    LOWER(LastName) AS LastName_Lower,
6    LEN(FirstName) AS FirstNameLength
7FROM Person.Person
8WHERE PersonType = 'EM'
9ORDER BY LastName;

SQL

1-- Extract first 3 characters của product number
2SELECT 
3    ProductID,
4    ProductNumber,
5    LEFT(ProductNumber, 2) AS ProductPrefix,
6    SUBSTRING(ProductNumber, 4, 4) AS ProductCode
7FROM Production.Product
8WHERE ProductNumber LIKE 'BK-%';

1.3 CONCAT và String Concatenation

SQL

1-- Method 1: + operator (NULL propagates)
2SELECT FirstName + ' ' + LastName AS FullName
3FROM Person.Person;
4
5-- Method 2: CONCAT (ignores NULL)
6SELECT CONCAT(FirstName, ' ', MiddleName, ' ', LastName) AS FullName
7FROM Person.Person;
8
9-- Method 3: CONCAT_WS (with separator, SQL 2017+)
10SELECT CONCAT_WS(' ', FirstName, MiddleName, LastName) AS FullName
11FROM Person.Person;

1.4 CHARINDEX và PATINDEX

SQL

1-- CHARINDEX: Tìm vị trí substring
2SELECT 
3    EmailAddress,
4    CHARINDEX('@', EmailAddress) AS AtPosition,
5    LEFT(EmailAddress, CHARINDEX('@', EmailAddress) - 1) AS Username,
6    SUBSTRING(EmailAddress, CHARINDEX('@', EmailAddress) + 1, 100) AS Domain
7FROM Person.EmailAddress;
8
9-- PATINDEX: Tìm pattern (supports wildcards)
10SELECT 
11    Name,
12    PATINDEX('%[0-9]%', Name) AS FirstDigitPosition
13FROM Production.Product
14WHERE PATINDEX('%[0-9]%', Name) > 0;

1.5 REPLACE và STUFF

SQL

1-- REPLACE: Thay thế tất cả occurrences
2SELECT 
3    Name,
4    REPLACE(Name, 'Mountain', 'MTN') AS ShortName
5FROM Production.Product
6WHERE Name LIKE '%Mountain%';
7
8-- STUFF: Thay thế tại vị trí cụ thể
9SELECT 
10    PhoneNumber,
11    STUFF(PhoneNumber, 1, 3, '***') AS MaskedPhone
12FROM Person.PersonPhone;

1.6 STRING_AGG (SQL 2017+)

SQL

1-- Combine products trong mỗi category thành 1 string
2SELECT 
3    pc.Name AS Category,
4    STRING_AGG(p.Name, ', ') WITHIN GROUP (ORDER BY p.Name) AS Products
5FROM Production.Product p
6JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
7JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
8WHERE p.ListPrice > 2000
9GROUP BY pc.ProductCategoryID, pc.Name;

1.7 FORMAT (cho strings)

SQL

1-- Format phone numbers
2SELECT 
3    PhoneNumber,
4    FORMAT(CAST(REPLACE(REPLACE(REPLACE(PhoneNumber, '-', ''), '(', ''), ')', '') AS BIGINT), '(###) ###-####') AS FormattedPhone
5FROM Person.PersonPhone
6WHERE LEN(REPLACE(REPLACE(REPLACE(PhoneNumber, '-', ''), '(', ''), ')', '')) = 10;

2. DateTime Functions

2.1 Get Current Date/Time

Function	Returns	Example Result
`GETDATE()`	Current datetime	2024-01-15 14:30:45.123
`GETUTCDATE()`	UTC datetime	2024-01-15 21:30:45.123
`SYSDATETIME()`	High precision datetime2	2024-01-15 14:30:45.1234567
`CURRENT_TIMESTAMP`	Same as GETDATE()	2024-01-15 14:30:45.123

SQL

1SELECT 
2    GETDATE() AS CurrentLocal,
3    GETUTCDATE() AS CurrentUTC,
4    SYSDATETIME() AS HighPrecision;

2.2 Date Parts Functions

SQL

1-- Extract parts
2SELECT 
3    OrderDate,
4    YEAR(OrderDate) AS Year,
5    MONTH(OrderDate) AS Month,
6    DAY(OrderDate) AS Day,
7    DATEPART(QUARTER, OrderDate) AS Quarter,
8    DATEPART(WEEK, OrderDate) AS WeekOfYear,
9    DATEPART(WEEKDAY, OrderDate) AS DayOfWeek,
10    DATENAME(MONTH, OrderDate) AS MonthName,
11    DATENAME(WEEKDAY, OrderDate) AS DayName
12FROM Sales.SalesOrderHeader
13WHERE SalesOrderID = 43659;

2.3 Date Calculations với DATEADD

SQL

1-- DATEADD(interval, number, date)
2SELECT 
3    OrderDate,
4    DATEADD(DAY, 7, OrderDate) AS Plus7Days,
5    DATEADD(MONTH, 1, OrderDate) AS Plus1Month,
6    DATEADD(YEAR, -1, OrderDate) AS Minus1Year,
7    DATEADD(QUARTER, 1, OrderDate) AS Plus1Quarter
8FROM Sales.SalesOrderHeader
9WHERE SalesOrderID = 43659;

Common intervals: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND

2.4 Date Difference với DATEDIFF

SQL

1-- Orders với số ngày để ship
2SELECT 
3    SalesOrderID,
4    OrderDate,
5    ShipDate,
6    DATEDIFF(DAY, OrderDate, ShipDate) AS DaysToShip,
7    DATEDIFF(HOUR, OrderDate, ShipDate) AS HoursToShip
8FROM Sales.SalesOrderHeader
9WHERE ShipDate IS NOT NULL
10ORDER BY DaysToShip DESC;

SQL

1-- Customer age analysis (days since first order)
2SELECT 
3    CustomerID,
4    MIN(OrderDate) AS FirstOrder,
5    MAX(OrderDate) AS LastOrder,
6    DATEDIFF(DAY, MIN(OrderDate), MAX(OrderDate)) AS CustomerLifespanDays,
7    DATEDIFF(MONTH, MIN(OrderDate), MAX(OrderDate)) AS CustomerLifespanMonths
8FROM Sales.SalesOrderHeader
9GROUP BY CustomerID
10HAVING COUNT(*) > 5
11ORDER BY CustomerLifespanDays DESC;

2.5 Date Formatting với FORMAT

SQL

1SELECT 
2    OrderDate,
3    FORMAT(OrderDate, 'yyyy-MM-dd') AS ISO_Format,
4    FORMAT(OrderDate, 'dd/MM/yyyy') AS EU_Format,
5    FORMAT(OrderDate, 'MMMM dd, yyyy') AS Long_Format,
6    FORMAT(OrderDate, 'yyyy-MM') AS YearMonth,
7    FORMAT(OrderDate, 'dddd') AS DayName
8FROM Sales.SalesOrderHeader
9WHERE SalesOrderID = 43659;

Common format patterns:

yyyy - 4-digit year
MM - 2-digit month
dd - 2-digit day
MMMM - Full month name
dddd - Full day name
HH:mm:ss - 24-hour time

2.6 EOMONTH (End of Month)

SQL

1-- End of month calculations
2SELECT 
3    OrderDate,
4    EOMONTH(OrderDate) AS EndOfMonth,
5    EOMONTH(OrderDate, 1) AS EndOfNextMonth,
6    EOMONTH(OrderDate, -1) AS EndOfPrevMonth,
7    DAY(EOMONTH(OrderDate)) AS DaysInMonth
8FROM Sales.SalesOrderHeader
9WHERE SalesOrderID = 43659;

2.7 Date Construction

SQL

1-- DATEFROMPARTS, DATETIME2FROMPARTS
2SELECT 
3    DATEFROMPARTS(2024, 1, 15) AS ConstructedDate,
4    DATETIME2FROMPARTS(2024, 1, 15, 14, 30, 0, 0, 0) AS ConstructedDateTime;

2.8 ISDATE Validation

SQL

1-- Check if string is valid date
2SELECT 
3    '2024-01-15' AS TestValue,
4    ISDATE('2024-01-15') AS IsValidDate,
5    ISDATE('invalid') AS InvalidDate,
6    ISDATE('2024-02-30') AS Feb30;

3. Practical DateTime Scenarios

3.1 Fiscal Year Analysis

SQL

1-- Assuming fiscal year starts April 1
2WITH FiscalData AS (
3    SELECT 
4        OrderDate,
5        TotalDue,
6        CASE 
7            WHEN MONTH(OrderDate) >= 4 THEN YEAR(OrderDate)
8            ELSE YEAR(OrderDate) - 1
9        END AS FiscalYear,
10        CASE 
11            WHEN MONTH(OrderDate) >= 4 THEN MONTH(OrderDate) - 3
12            ELSE MONTH(OrderDate) + 9
13        END AS FiscalMonth
14    FROM Sales.SalesOrderHeader
15)
16SELECT 
17    FiscalYear,
18    FiscalMonth,
19    SUM(TotalDue) AS Revenue
20FROM FiscalData
21GROUP BY FiscalYear, FiscalMonth
22ORDER BY FiscalYear, FiscalMonth;

3.2 Same Day Last Year Comparison

SQL

1WITH CurrentYearSales AS (
2    SELECT 
3        CAST(OrderDate AS DATE) AS OrderDate,
4        SUM(TotalDue) AS Revenue
5    FROM Sales.SalesOrderHeader
6    WHERE YEAR(OrderDate) = 2014
7    GROUP BY CAST(OrderDate AS DATE)
8),
9PreviousYearSales AS (
10    SELECT 
11        CAST(OrderDate AS DATE) AS OrderDate,
12        SUM(TotalDue) AS Revenue
13    FROM Sales.SalesOrderHeader
14    WHERE YEAR(OrderDate) = 2013
15    GROUP BY CAST(OrderDate AS DATE)
16)
17SELECT 
18    c.OrderDate AS CurrentDate,
19    c.Revenue AS CurrentRevenue,
20    p.Revenue AS PrevYearRevenue,
21    c.Revenue - ISNULL(p.Revenue, 0) AS YoY_Change
22FROM CurrentYearSales c
23LEFT JOIN PreviousYearSales p ON DATEADD(YEAR, -1, c.OrderDate) = p.OrderDate
24ORDER BY c.OrderDate;

3.3 Business Days Calculation

SQL

1-- Orders on weekdays only
2SELECT 
3    OrderDate,
4    DATENAME(WEEKDAY, OrderDate) AS DayName,
5    TotalDue
6FROM Sales.SalesOrderHeader
7WHERE DATEPART(WEEKDAY, OrderDate) NOT IN (1, 7)  -- Exclude Sat/Sun
8AND YEAR(OrderDate) = 2013
9ORDER BY OrderDate;

3.4 Rolling 30-Day Analysis

SQL

1-- Orders in last 30 days (from a reference date)
2DECLARE @ReferenceDate DATE = '2014-06-30';
3
4SELECT 
5    CustomerID,
6    COUNT(*) AS OrderCount,
7    SUM(TotalDue) AS TotalRevenue
8FROM Sales.SalesOrderHeader
9WHERE OrderDate BETWEEN DATEADD(DAY, -30, @ReferenceDate) AND @ReferenceDate
10GROUP BY CustomerID
11ORDER BY TotalRevenue DESC;

4. Data Type Conversion

4.1 CAST và CONVERT

SQL

1-- CAST syntax
2SELECT 
3    CAST(ListPrice AS VARCHAR(20)) AS PriceString,
4    CAST('2024-01-15' AS DATE) AS DateValue,
5    CAST(123.456 AS INT) AS IntValue
6FROM Production.Product
7WHERE ProductID = 1;
8
9-- CONVERT with style codes
10SELECT 
11    OrderDate,
12    CONVERT(VARCHAR(10), OrderDate, 101) AS US_Format,      -- mm/dd/yyyy
13    CONVERT(VARCHAR(10), OrderDate, 103) AS UK_Format,      -- dd/mm/yyyy
14    CONVERT(VARCHAR(10), OrderDate, 112) AS ISO_Compact,    -- yyyymmdd
15    CONVERT(VARCHAR(20), OrderDate, 120) AS ISO_Full        -- yyyy-mm-dd hh:mi:ss
16FROM Sales.SalesOrderHeader
17WHERE SalesOrderID = 43659;

4.2 TRY_CAST và TRY_CONVERT

SQL

1-- Safe conversion (returns NULL instead of error)
2SELECT 
3    TRY_CAST('123' AS INT) AS ValidInt,
4    TRY_CAST('abc' AS INT) AS InvalidInt,
5    TRY_CONVERT(DATE, '2024-01-15') AS ValidDate,
6    TRY_CONVERT(DATE, 'not a date') AS InvalidDate;

4.3 PARSE và TRY_PARSE

SQL

1-- Parse with culture
2SELECT 
3    TRY_PARSE('15/01/2024' AS DATE USING 'en-GB') AS UK_Date,
4    TRY_PARSE('01/15/2024' AS DATE USING 'en-US') AS US_Date,
5    TRY_PARSE('$1,234.56' AS MONEY USING 'en-US') AS US_Money;

5. NULL Handling

5.1 ISNULL, COALESCE, NULLIF

SQL

1SELECT 
2    ProductID,
3    Name,
4    Color,
5    -- ISNULL: Replace NULL with default
6    ISNULL(Color, 'No Color') AS ColorWithDefault,
7    
8    -- COALESCE: First non-NULL value
9    COALESCE(Color, Size, 'Unknown') AS FirstNonNull,
10    
11    -- NULLIF: Return NULL if equal
12    NULLIF(Color, 'Black') AS NotBlack
13FROM Production.Product
14WHERE ProductSubcategoryID = 1;

5.2 NULL in Calculations

SQL

1-- NULL với dates
2SELECT 
3    SalesOrderID,
4    OrderDate,
5    ShipDate,
6    DATEDIFF(DAY, OrderDate, ISNULL(ShipDate, GETDATE())) AS DaysToShipOrNow
7FROM Sales.SalesOrderHeader;

6. Thực hành

Exercises

Exercise 1: String Manipulation

SQL

1-- Tạo email từ FirstName và LastName: firstname.lastname@adventure-works.com
2-- Hiển thị: FullName, GeneratedEmail
3
4-- YOUR CODE HERE

💡 Xem đáp án

SQL

1SELECT 
2    FirstName + ' ' + LastName AS FullName,
3    LOWER(FirstName) + '.' + LOWER(LastName) + '@adventure-works.com' AS GeneratedEmail
4FROM Person.Person
5WHERE PersonType = 'EM'
6ORDER BY LastName, FirstName;

Exercise 2: Date Analysis

SQL

1-- Phân tích orders theo Day of Week
2-- Hiển thị: DayName, OrderCount, TotalRevenue
3
4-- YOUR CODE HERE

💡 Xem đáp án

SQL

1SELECT 
2    DATENAME(WEEKDAY, OrderDate) AS DayName,
3    DATEPART(WEEKDAY, OrderDate) AS DayNumber,
4    COUNT(*) AS OrderCount,
5    SUM(TotalDue) AS TotalRevenue
6FROM Sales.SalesOrderHeader
7GROUP BY DATENAME(WEEKDAY, OrderDate), DATEPART(WEEKDAY, OrderDate)
8ORDER BY DayNumber;

Exercise 3: Age Calculation

SQL

1-- Tính tuổi employees (từ BirthDate đến GETDATE())
2-- Hiển thị: EmployeeName, BirthDate, Age
3
4-- YOUR CODE HERE

💡 Xem đáp án

SQL

1SELECT 
2    p.FirstName + ' ' + p.LastName AS EmployeeName,
3    e.BirthDate,
4    DATEDIFF(YEAR, e.BirthDate, GETDATE()) - 
5        CASE 
6            WHEN DATEADD(YEAR, DATEDIFF(YEAR, e.BirthDate, GETDATE()), e.BirthDate) > GETDATE()
7            THEN 1 
8            ELSE 0 
9        END AS Age
10FROM HumanResources.Employee e
11JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
12ORDER BY Age DESC;

Exercise 4: Monthly Report Format

SQL

1-- Format monthly sales report
2-- Hiển thị: MonthYear (format: 'January 2014'), Revenue (format: $1,234,567.89)
3
4-- YOUR CODE HERE

💡 Xem đáp án

SQL

1SELECT 
2    FORMAT(OrderDate, 'MMMM yyyy') AS MonthYear,
3    FORMAT(SUM(TotalDue), 'C', 'en-US') AS Revenue
4FROM Sales.SalesOrderHeader
5GROUP BY YEAR(OrderDate), MONTH(OrderDate), FORMAT(OrderDate, 'MMMM yyyy')
6ORDER BY YEAR(OrderDate), MONTH(OrderDate);

7. Tổng kết

String Functions Quick Reference

Category	Functions
Length/Extract	LEN, DATALENGTH, LEFT, RIGHT, SUBSTRING
Modify	UPPER, LOWER, TRIM, REPLACE, STUFF
Search	CHARINDEX, PATINDEX
Combine	CONCAT, CONCAT_WS, STRING_AGG

DateTime Functions Quick Reference

Category	Functions
Current	GETDATE, GETUTCDATE, SYSDATETIME
Extract	YEAR, MONTH, DAY, DATEPART, DATENAME
Calculate	DATEADD, DATEDIFF, EOMONTH
Format	FORMAT, CONVERT
Construct	DATEFROMPARTS, DATETIME2FROMPARTS

CONVERT Style Codes (Common)

Code	Format	Example
101	mm/dd/yyyy	01/15/2024
103	dd/mm/yyyy	15/01/2024
112	yyyymmdd	20240115
120	yyyy-mm-dd hh:mi:ss	2024-01-15 14:30:00

Bài tiếp theo: CASE và Conditional Logic