[00:00]

Introduction to SQL Functions

 Definition of function: A built-in code block in SQL that accepts an input value, processes it,
and returns an output value after performing some transformation.

 Purpose of using functions:

o Data manipulation (changing data values)

o Data aggregation and analysis (summarizing or extracting insights)

o Data cleansing (fixing or removing bad data)

o Data transformation to solve specific SQL tasks or queries

 SQL functions enable users to systematically interact with data stored in tables to prepare or
analyze it efficiently.

[00:56]
Categories of SQL Functions
SQL functions are broadly categorized into two types based on their input-output behavior:

Function Output Conceptual

Input Description Example
Type Description Description

Single-row One single value One single value 1 value in → 1 value

LEFT('Maria',2) → 'Ma'
Functions (one row) (one row) out

Multi-row Multiple values One single Multiple values in → 1

SUM(30,10,20,40) → 100
Functions (multiple rows) summary value summarized output

 Single-row functions: Process individual data entries.

 Multi-row functions: Aggregate data across multiple rows or records.

[01:54]
Nesting SQL Functions: Composition and Execution Order

 SQL allows combining multiple functions by nesting; the output of one function serves as the
input to another.

 Example given:

1. LEFT('Maria', 2) extracts 'Ma'

2. LOWER('Ma') converts 'Ma' to 'ma'

3. LENGTH('ma') returns 2

 Conceptual analogy: Data flows through processing stages (like factory stations), with each
function modifying the intermediate result.

 Writing nested functions:

o Start innermost function first.

o Wrap it inside parentheses and place inside the next function call.
 o Continue wrapping if more functions are added.

 Execution order: From innermost to outermost function, e.g.,

LENGTH(LOWER(LEFT(Maria,2)))

which executes LEFT → LOWER → LENGTH sequentially.

[04:02]
Subcategories of SQL Functions Based on Data Type and Usage
Within the two main functional categories, functions are further subdivided:

Category Subcategory Purpose/Use Case

- String functions
Single-row - Numeric functions Used for cleaning, transforming, and preparing
functions - Date & Time functions individual data values before aggregation
- Null-handling functions

- Aggregate functions (basic

Multi-row summaries) Used primarily for summarizing, grouping, or
functions - Window/Analytical functions advanced analysis over multi-row datasets
(advanced analytics)

 Aggregate functions: Basic operations like SUM, AVG, COUNT, etc.

 Window functions (Analytical functions): Advanced functions that allow perform

calculations across a set of rows related to the current row, useful for analytics beyond simple
aggregation.

[04:26]
Role of SQL Functions in Data Engineering and Data Analysis

 Single-row functions:

o Mainly used by Data Engineers to prepare data through cleaning, transformation,

and manipulation for further analysis.

o These functions ensure data integrity and reformatting on a row-by-row basis.

 Multi-row functions:

o Primarily used by Data Analysts for data summarization and reporting tasks through
aggregate and window functions.

 This logical division promotes efficient data workflows: engineers prepare the dataset,
analysts interpret and use it.

[04:53]
Course Structure Announcement and Next Steps

 The instructional plan includes:

o Systematic exploration of each subgroup of functions one by one.

o In-depth understanding of how each function works.

 o Practical examples and scenarios illustrating when and why to use specific functions.

 The course begins with string functions, teaching how to manipulate character data values
in SQL.

[00:00]
Summary: Overview of String Data Transformation Functions in SQL

 The video focuses on transforming string data using SQL string functions.

 String functions are categorized into three groups:

1. Manipulation functions: e.g., CONCAT, UPPER, LOWER, REPLACE

2. Calculation function: specifically LENGTH

3. Extraction functions: LEFT, RIGHT, SUBSTRING

 Each group addresses different needs: combining strings, character case transformation,
cleansing spaces, replacing values, counting characters, and extracting partial strings.

[00:26]
String Manipulation: Concatenation (CONCAT)

 Purpose: Combine multiple string values into a single string.

 Example use case: Joining first name and last name stored in separate columns into one full
name.

 SQL usage:

sqlCopy

SELECT CONCAT(first_name, last_name) AS full_name FROM customers;

 Adding separators (such as spaces, hyphens, or underscores) between concatenated values

improves readability:

sqlCopy

SELECT CONCAT(first_name, ' ', country) AS name_country FROM customers;

 Key Insight: Concatenation aids in data presentation and easier analysis by merging related
string data into one field.

[03:10]
Changing String Case: UPPER and LOWER Functions

 UPPER(string): Converts all characters in the input string to uppercase.

 LOWER(string): Converts all characters in the input string to lowercase.

 Example transformations:

o Input: "Maria" → UPPER → "MARIA", LOWER → "maria"

 o Input: "JOHN" → LOWER → "john", UPPER → "JOHN" (no change as already
uppercase)

 Practical task examples involved transforming a customer's first name entirely to lowercase or
uppercase.

 SQL example:

sqlCopy

SELECT LOWER(first_name) AS low_name, UPPER(first_name) AS up_name FROM customers;

 Conclusion: These functions standardize data for consistency in queries and reports.

[06:01]
Removing Unwanted Spaces: The TRIM Function

 Purpose: Eliminate leading and trailing spaces (whitespace) from string values.

 Problem overview:

o Strings can have unwanted spaces at the start (leading), at the end (trailing), or both.

o Spaces may be multiple consecutive spaces.

 TRIM(string) removes all such extra spaces, which is essential for cleaning dirty or
inconsistent input data.

 Detection of leading/trailing spaces can be performed by comparing:

o The original string and the trimmed string for inequality.

o Length before trimming vs. length after trimming.

 Example SQL for detecting rows with spaces:

sqlCopy

SELECT first_name FROM customers

WHERE first_name <> TRIM(first_name);

 Another approach involves comparing lengths:

sqlCopy

LENGTH(first_name) <> LENGTH(TRIM(first_name))

 Key Insight: The TRIM function is fundamental for data cleansing to avoid errors caused by
invisible whitespaces.

[11:21]
Replacing Characters: The REPLACE Function

 Purpose: Substitute all occurrences of a specified substring within a string with another
substring.

 Syntax:

sqlCopy
REPLACE(string, old_substring, new_substring)

 Usage examples:

o Replace dashes (-) in a phone number with slashes (/):

 Input: "123-456-7890"

 Output: "123/456/7890"

o Remove characters by replacing with an empty string ('').

 Removes unwanted characters like dashes completely.

o Change file extensions in filenames:

 Input: "[Link]"

 Replace .txt with .csv

 Output: "[Link]"

 Conclusion: REPLACE is a versatile function widely used for formatting and data correction.

[14:39]
Calculations on Strings: LENGTH Function

 Purpose: Calculates the total number of characters in a string, including letters, digits,
punctuation, and spaces.

 Can be applied to:

o Plain text

o Numeric values (treated as text)

o Dates or formatted strings with special characters like dashes or underscores

 Example:

o Input: "Maria" → 5

o Input: "350" → 3

o Input: "2026-01-23" → 10 (counts dashes as characters)

 SQL example to calculate length of first names:

sqlCopy

SELECT LENGTH(first_name) AS length_name FROM customers;

 Insight: Useful for validating data format, identifying anomalies, and supporting substring
extraction.

[16:29]
Extracting Parts of Strings: LEFT, RIGHT, and SUBSTRING Functions

 LEFT(string, n)
Extracts the first 𝑛 characters from the left (start) of the string.
 Example:
Input: "Maria", LEFT(string, 2) → "Ma"

 RIGHT(string, n)
Extracts the last 𝑛 characters from the right (end) of the string.
Example:
Input: "Maria", RIGHT(string, 2) → "ia"

 Both require the full string and the number of characters to extract.

 SUBSTRING(string, start_position, length)

Extracts a substring beginning at a specific position for a given length.
Example scenario:

o Task: Extract 2 characters after the 2nd position in "Maria"

o Counting positions (1-based indexing): 𝑀 = 1, 𝑎 = 2, start at 3 → Extract "ri"

o SQL:

sqlCopy

SUBSTRING('Maria', 3, 2) -- returns "ri"

 Handling variable length strings with SUBSTRING can be tricky; to include all characters
starting at a position to end of string, use a large number or dynamically calculate length via
the LENGTH function.

 To remove the first character from a string efficiently:

sqlCopy

SUBSTRING(TRIM(first_name), 2, LENGTH(TRIM(first_name)))

 Key Insight:

o LEFT and RIGHT are ideal for extracting from fixed ends.

o SUBSTRING allows more flexible extractions from the middle.

[22:54]
Practical SQL Examples and Tips

 Combining functions for complex tasks is common:

o Use TRIM inside SUBSTRING to ensure unwanted spaces do not affect substring
extraction.

o Use LENGTH to dynamically specify the length in SUBSTRING to handle variable-

length strings.

 Example to extract all characters after the first character:

sqlCopy

SELECT SUBSTRING(TRIM(first_name), 2, LENGTH(TRIM(first_name))) AS subname FROM

customers;

 This approach is dynamic and robust across varied string lengths.

 [25:48]
Closing Notes

 A variety of string manipulation functions have been covered which provide powerful tools to
clean, format, combine, and extract string data in SQL.

 These skills are essential for data preparation and reporting.

 Upcoming tutorials will cover numeric data manipulation functions.

 Encouragement given to support the channel through likes, subscriptions, and sharing.

Summary Table: Key String Functions Covered

Function Purpose Parameters Notes

Join multiple strings Can include separators (spaces,

CONCAT CONCAT(string1, string2, ...)
into one string etc.)

Convert all characters

UPPER UPPER(string) Useful for data standardization
to uppercase

Convert all characters

LOWER LOWER(string) Useful for data standardization
to lowercase

Remove leading and Cleans data, removes unwanted

TRIM TRIM(string)
trailing spaces whitespaces

Substitute old
REPLACE(string, Can also remove characters by
REPLACE substring with new
old_substring, new_substring) replacing with empty string
one

Count number of Includes letters, digits, spaces,

LENGTH LENGTH(string)
characters in a string symbols

Extract characters
LEFT LEFT(string, n) Extracts 𝑛 leftmost characters
from start of string

Extract characters
RIGHT RIGHT(string, n) Extracts 𝑛 rightmost characters
from end of string

Extract characters SUBSTRING(string, start_pos,

SUBSTRING Flexible extraction from middle
from specific position length)

[00:00]
This segment introduces the topic of transforming numeric values using two simple SQL
 functions: ROUND and ABS (absolute value). The main focus is on explaining how
the ROUND function operates, using the example number 3.516. Key points include:

 Applying ROUND with two decimal places means keeping two digits after the decimal point.

 The third decimal digit (6 in this case) determines if the second digit will round up or stay.

 Since the third digit (6) is greater than 5, the value rounds up, turning 3.516 into 3.52.

 The function effectively "cuts off" or resets digits beyond the specified decimal place.

[00:33]
Further details about the ROUND function are explained with different decimal places for
rounding:

 Rounding to one decimal place: The second digit (1) after the decimal decides whether to
round up the first digit (5).

 Since 1 is less than 5, there is no rounding up, so the number becomes 3.5.

 Digits after the maintained decimal place are reset to zero (effectively removed).

 Rounding to zero decimals: This means rounding the value to the nearest integer.

 The first digit after the decimal (5) determines if the integer part (3) should round up.

 Since it is 5 (or above), the integer part rounds up to 4, with all decimal digits removed.

The overall mechanism of how the ROUND function decides whether to round up or stay the
same depending on the digit following the rounding precision is emphasized.

[01:33]
The instructor transitions to how the ROUND function works in SQL practically:

 An example using static values (not directly querying a database table) demonstrates the
function.

 This allows practicing on fixed numbers like 3.516 without requiring table data.

 This method is used to conveniently understand and test the numeric functions.

[02:00]
A live SQL example of the ROUND function is demonstrated:

 The value 3.516 is rounded to two decimal places using ROUND.

 The output correctly shows 3.52, as explained earlier (since the third decimal digit 6 causes
rounding up).

This confirms the theoretical explanation with practical SQL code and output verification.

[02:33]
Further SQL executions test the ROUND function with different decimal places:

Decimal
Input Output Explanation
Places

Second digit after decimal is 1, no

1 3.516 3.5
rounding up
 Decimal
Input Output Explanation
Places

First digit after decimal is 5, rounds up to

0 3.516 4
next integer

Key takeaway: The rounding logic strictly aligns with the digit immediately after the
rounding position.

[03:07]
The video introduces the second numeric function: ABS (absolute value):

 This function converts any negative number into its positive counterpart.

 Example demonstrated: ABS(-10) returns 10.

 Explains that if the number is already positive, the function returns it as is.

This function is essential for data correction scenarios, for instance:

 If a data set has logically invalid negative sales figures, using ABS can normalize all values to
positive.

Function Purpose Example Output Notes

Rounds up
Round or down
numeric based on
values to ROUND(3.516, digit
ROUND 3.52
specified 2) immediately
decimal after
places rounding
position

No
ROUND(3.516, rounding if
3.5
1) next digit
below 5

Rounds to
ROUND(3.516,
4 nearest
0)
integer
 Converts
Converts
a
negatives
number
to positive,
ABS to its ABS(-10) 10
leaves
absolute
positives
(positive)
unchanged
value

The instructor highlights the practical usefulness of this function to transform and sanitize
numerical data in databases.

[04:04]
The conclusion wraps up the numeric functions section:

 Only two simple but important functions — ROUND and ABS — have been covered.

 The next video will shift focus to manipulating date and time data in SQL, suggesting the
continuity of learning SQL functions.

Summary Table of Functions Covered

Key Insights

 The ROUND function uses the digit immediately after the indicated decimal place to
determine rounding behavior: if it is 5 or above, the number rounds up; otherwise, it
remains.

 Setting the decimal places to zero (0) rounds the number to the nearest integer.

 The ABS function ensures all numbers are positive, which is crucial when handling
numeric data that logically cannot have negative values (e.g., sales figures).

 Practicing with static values inside SQL allows learners to understand and verify
function behaviors without needing actual database tables.

 Mastery of these foundational numerical functions prepares one for more complex SQL
operations, such as date/time functions, which are hinted as the next topic.

Conclusion

This video provides a concise yet thorough explanation and demonstration of

the ROUND and ABS SQL functions. It builds foundational understanding of how to
manipulate numeric data within SQL queries effectively. These functions are critical for data
cleaning, presentation, and ensuring data validity. Understanding decimal rounding logic and
absolute value transformations equips learners with practical tools for everyday SQL
programming. The session closes by preparing the viewer for the upcoming topic on date and
time manipulation in SQL.

[00:00]

Introduction to Date and Time in SQL

 The video begins with a foundational overview of date and time concepts in SQL.

 Date represents calendar dates like "August 20th, 2025," composed of three parts:

o Year: four-digit number, e.g., 2025

 o Month: numeric from 1 to 12

o Day: numeric from 1 to 31

 In databases, this structure is stored as a DATE data type.

[01:12]

 Time refers to a specific point within a day, including:

o Hours: 0 to 23

o Minutes: 0 to 59

o Seconds: 0 to 59

 This structure is stored as a TIME data type in databases.

[01:44]

 Combining date and time gives rise to:

o TIMESTAMP (in Oracle, PostgreSQL, MySQL) or DATETIME (in SQL Server), which
includes year, month, day, hour, minute, second, and fractions of seconds.

 Hierarchical order: Year > Month > Day > Hour > Minute > Second

 Summary:

Data Type Components Databases Example

DATE Year, Month, Day All major DBMS

Hours, Minutes,
TIME All major DBMS
Seconds

Oracle, PostgreSQL,
Date + Time (including
TIMESTAMP/DATETIME MySQL, SQL Server
fractions of seconds)
(DATETIME)

[02:44]

Accessing Date and Time Data in a Database

 Example with a [Link] table:

o Order Date and Shipping Date columns use DATE type, storing only date without
time.

o Creation Date column uses DATETIME/ TIMESTAMP type, storing full date and time
including milliseconds.

 Querying shows date-only columns return only year, month, day, whereas datetime columns
include hours, minutes, seconds, and fractions.

[04:18]

Sources of Date and Time in SQL Queries

 The three primary sources for date/time values in SQL queries are:
 1. Stored dates inside the database, e.g., order date columns.

2. Hardcoded date strings (static values inside queries), e.g., '2025-08-20'. These are
fixed and used for calculations or filters.

3. Current date and time via SQL functions, chiefly the GETDATE() function, which
returns the current date and time at query execution.

 GETDATE() accepts no parameters and returns a DATETIME value. It's frequently used
throughout SQL operations.

[06:50]

Ways to Manipulate Dates in SQL — Overview

Main categories of manipulation:

 Part Extraction: Retrieve specific parts like year, month, day.

 Format and Casting: Change how the date/time is displayed or its data type.

 Date Calculations: Add or subtract intervals, find differences.

 Validation: Test if a given date is valid.

[08:13]

 Four categories of SQL date/time functions covered:

Category Functions Included

Part
DAY(), MONTH(), YEAR(), DATEPART(), DATENAME(), DATE_TRUNC(),
Extraction

Formatting
FORMAT(), CONVERT(), CAST()
& Casting

Calculations DATEADD(), DATEDIFF()

Validation ISDATE()

[09:06]

Part Extraction Functions: DAY, MONTH, YEAR

 Simple and straightforward functions that accept one DATE or DATETIME parameter and
return an integer representing the corresponding part.

 Example results from the date '2025-08-20':

 o DAY() returns 20

o MONTH() returns 8

o YEAR() returns 2025

 Syntax summary:
DAY(date)

MONTH(date)

YEAR(date)

 Practical examples demonstrated querying these from the creation_time column to extract the
year, month, and day as integers.

[12:21]

Expanded Extraction with DATEPART() Function

 DATEPART(part, date) allows extracting various date/time components beyond day, month,
and year, including:

o Week number, quarter, hour, minute, second, etc.

 Returns an integer value for the specified part.

 Supports abbreviations: for example, mm for month, wk for week.

 Examples:

o DATEPART(week, creation_time) returns the week number.

o DATEPART(quarter, creation_time) returns the quarter number.

o DATEPART(hour, creation_time) returns the hour of the day.

 Syntax:
DATEPART(part,date)

 Output data type: integer.

[18:06]

DATENAME() Function — Extracting the Name Instead of Number

 Retrieves the name of a specified date part instead of a number (as in DATEPART).

 Examples:

o DATENAME(month, date) returns "August" instead of 8.

o DATENAME(weekday, date) returns "Wednesday" instead of a number.

 The returned data type is string.

 For parts like year or day of month, it still returns the numeric value but as a string.
 Syntax:
DATENAME(part,date)

 Used primarily to display human-readable labels in reports or user interfaces.

[22:32]

DATE_TRUNC() Function — Truncating Dates to a Specific Precision

 Truncates the date/time to a specified part by resetting lower granularity parts to default
values.

 The function accepts part and date parameters similar to DATEPART and DATENAME.

 It "zooms out" the date/time to a higher-level aggregation unit.

 Example behavior on a datetime '2025-08-20 18:55:45':

Truncation
Result Explanation
Part

'2025-08-20
minute Seconds reset to zero
18:55:00'

'2025-08-20
hour Minutes and seconds reset
18:00:00'

'2025-08-20
day Time parts reset
00:00:00'

'2025-08-01
month Day reset to 1, time reset
00:00:00'

'2025-01-01 Month and day reset to January 1, time

year
00:00:00' reset

 The output type is always DATETIME/TIMESTAMP.

 Widely used for data aggregation at different granularities (group by year, month, day).

[29:11]

Real-World Example: Aggregating Orders by Date Granularity Using DATE_TRUNC

 Grouping by exact creation time yields many rows with little aggregation (each second is
unique).

 Truncating to month or year allows grouping to fewer entries with summarized counts:
 Result Example (# of
Granularity Explanation
rows)

Exact High granularity, low

Many (one per record)
date/time aggregation

Groups all days within each

Month Few (e.g., 3 months)
month

Groups all months into one

Year One (e.g., 2025)
year

 Demonstrates the power and usefulness of date truncation in analytics.

[31:09]

EOMONTH() Function — Finding the Last Day of the Month

 Returns the last day of the month for a given date.

 Examples:

o EOMONTH('2025-08-20') → '2025-08-31'

o EOMONTH('2025-02-01') → '2025-02-28' (considering non-leap year)

o If the input date is already the last day, it returns the same date.

 The output is a DATE data type (date only, no time).

[33:09]

Getting First Day of the Month Using DATE_TRUNC()

 No direct function like EOMONTH() for first day, but can be generated by truncating at
the month level:
DATE_TRUNC(’month’,date)

 Results in the first day of the month at time 00:00:00.

 To show only the date (no time), cast the result to DATE:

sqlCopy

CAST(DATE_TRUNC('month', date) AS DATE)

 This method combined with EOMONTH() allows retrieval of start and end of month values in
reports.

[34:37]

Use Cases for Extracting Date Parts

 Data Aggregation & Reporting:

o Summarize by year, quarter, month, or other parts to generate meaningful KPI

reports.
 o Drilldowns and rollups based on date components offer flexible analysis.

 Filtering Data:

o For example, selecting orders placed only in February using:

sqlCopy

WHERE MONTH(order_date) = 2

 Best practice: filter using integer-valued functions (MONTH(), YEAR()), not string-valued
(DATENAME()) for performance.

[39:41]

Data Types of Function Outputs Summary

Output Data
Function(s) Description
Type

Numeric parts extracted

DAY(), MONTH(), YEAR(), DATEPART() Integer
from dates

Human-readable name of
DATENAME() String parts (month names,
weekday names)

Date truncated to
DATETIME /
DATE_TRUNC() specified part, includes
TIMESTAMP
time

Last day of the month,

EOMONTH() DATE
date only

 Understanding output data type helps avoid implicit conversion issues.

[40:37]

Recommendations on When to Use Which Function

 If you want a numeric part directly (e.g., year, month, day):

o Use YEAR(), MONTH(), DAY() functions.

 If you want the full name (e.g., "August," "Wednesday"):

o Use DATENAME().

 If you want to extract other parts like week number, quarter, hour, minute, second:

o Use DATEPART().

 Use DATE_TRUNC() for truncating or grouping data by a specific level of detail for analysis.

[41:39]

Comprehensive Table of Date Parts Across Functions

 Part DATEPART() Output DATENAME() Output DATE_TRUNC()

year Integer (e.g., 2024) String (e.g., "2024") Truncates date to

Truncates date to
quarter Not speci
String (same as
quarter Integer (1-4) (function typically
integer)
truncates to mont
in practice)

month Integer (1-12) String (e.g., "August") Truncates date to

String (same as
day Integer (1-31) Truncates date to
integer)

String (same as
week Integer (1-53) Not applicable
integer)

String (e.g.,
weekday Integer (1-7) Not applicable
"Monday")

String (same as
hour Integer (0-23) Truncates time to
integer)

String (same as Truncates time to

minute Integer (0-59)
integer) minute

String (same as Truncates time to

second Integer (0-59)
integer) second

 Note: Not all parts apply exactly the same to DATE_TRUNC() due to truncation logic.

[42:06]

Practice Queries and Tutorial Materials

 The video provides downloadable SQL scripts demonstrating all functions and date parts
combined in one query for practice.

 The example uses GETDATE() as the input date to show outputs of all extraction methods
simultaneously.

 Encourages hands-on learning to familiarize with output formats and function behaviors.

[43:05]

Conclusion

 The tutorial covers manipulating date and time in SQL focusing on 13 key functions
categorized as extraction, formatting, calculation, and validation.
 The instructor invites viewers to subscribe, like, and share for future SQL tutorials.

00:04]

Understanding Date Formats in SQL

 Date format consists of date and time components such as year, month, day, hour, minute,
and second, which are represented by a combination of numbers and characters.

 Characters like dashes (“-”) or spaces between components are part of the date format and
must be represented explicitly in SQL formatting.

 Format specifiers are the symbolic codes used in SQL to represent parts of the date/time:

o Year: four digits represented as YYYY or yyyy (case sensitive).

o Month: two digits represented as MM (uppercase).

o Day: two digits represented as DD (uppercase).

o Hour: two digits, usually HH for 24-hour format; can also use lowercase for 12-hour
format.

o Minutes: two digits represented as mm (lowercase).

o Seconds: two digits represented as ss (lowercase).

 Key Insight: The date/time format in SQL is case sensitive—for instance,

uppercase M means month, whereas lowercase m means minutes.

[01:38]

Global Date Format Standards and SQL Defaults

 Different countries use different conventions to represent dates:

o ISO 8601 (International Standard): YYYY-MM-DD (Year-Month-Day) — default in

SQL Server.

o USA format: MM-DD-YYYY (Month-Day-Year)

o European format: DD-MM-YYYY (Day-Month-Year), which is the reverse order of

the ISO standard.

 SQL Server adopts ISO 8601, so all date values in SQL databases default to Year-Month-
Day ordering.

[03:10]

Formatting vs. Casting in SQL

 Formatting changes the appearance or presentation of data without altering its underlying
data type.

o Example: Converting 2025-04-15 to 04/15/25 (month/day/two-digit year).

o The FORMAT() function in SQL is used for this purpose, taking parameters for value,
format string, and optionally culture (regional formatting).
 o The CONVERT() function can also perform formatting but uses a style
number instead of a format string.

 Casting changes the data type of a value (e.g., string to integer, date to string).

o Functions supporting casting include CAST() and CONVERT().

o Casting actually changes the stored type; formatting just changes how it looks.

[04:06]

Examples of Using FORMAT() Function

 FORMAT(value, format_string [, culture])

o Example: FORMAT(order_date, 'MM/dd/yy') outputs a date in USA style with two-digit

year.

o Using culture parameter (optional) can specify localization, e.g., "ja-JP" for Japanese
style or "fr-FR" for French, which affects date, time, and number formatting.

 Format specifiers are customizable and can be used for both dates and numbers:

o Numbers: "N" formats as numeric with commas, "C" for currency (adds dollar
sign), "P" for percentage (adds percent sign).

o Dates: Specifiers such as DD, DDD, DDDD provide day digits, abbreviated day
name, and full day name respectively.

[07:56]

Deep Dive into Date Format Specifiers

 Day formats:

Format Output Example Description

DD 01, 05 Two-digit day with leading zero

DDD Mon, Tue Abbreviated name of day

DDDD Monday Full name of the day

 Month formats:

Format Output Example Description

MM 01, 12 Two-digit month

MMM Jan, Feb Abbreviated month name

MMMM January Full month name

 Switching formats allows creation of different regional date styles such as:

o USA: MM-DD-YYYY

o Europe: DD-MM-YYYY

[10:26]

Constructing Complex Custom Date Formats

 Combine static text, format specifiers, and functions to build custom date/time strings.

 Example construction:

o Start with string “day ” (static), concatenated with short day name (DDD), abbreviated
month (MMM), quarter (Q1, Q2, etc.), year, 12-hour time (hh:mm:ss), and AM/PM
designator (tt).

 Quarter extraction requires a function such as DATEPART(quarter,

<date>) or DATENAME(quarter, <date>) since it’s not directly a format specifier.

 Example pattern:

Copy

'day ' + FORMAT(creation_time, 'DDD') + ' ' + FORMAT(creation_time,'MMM')

+ ' Q' + CAST(DATEPART(quarter, creation_time) AS VARCHAR) + ' '

+ FORMAT(creation_time, 'yyyy hh:mm:ss tt')

 This approach provides flexibility to generate unconventional date/time formats for reporting
or display.

[15:20]

Use Case: Formatting for Aggregations and Reporting

 Formatting dates to specific granularities helps in grouping and aggregating data

meaningfully.

 Example: Sales aggregated by month can display months as abbreviated names plus two-
digit year (e.g., Jan 25).

 Using FORMAT(order_date, 'MMM yy') in GROUP BY clause:

sqlCopy

SELECT FORMAT(order_date, 'MMM yy') AS order_month, COUNT(*) AS number_of_orders

FROM sales_orders

GROUP BY FORMAT(order_date, 'MMM yy')

 Gives readable, customized grouped output instead of raw date values.

 Practical insight: Unifying date formats across different data sources (CSV files, APIs,
databases) is crucial for consistent analytics and reporting.
 [17:18]

SQL Date and Number Format Specifiers Overview

 SQL provides a comprehensive set of format specifiers for both dates and numbers.

 Date/time format specifiers are case sensitive and each corresponds to precise date/time
parts or textual representations.

 Number format specifiers include options for numeric (N), currency (C), percentage (P), and
cultural variants.

 Resource available: Pre-prepared queries listing all possible date and number format
specifiers, which users can run live in SQL to explore outputs.

[18:47]

CONVERT() Function - Syntax and Usage

 CONVERT(data_type, value [, style])

o Converts value to the specified data_type.

o Optional style parameter allows formatting dates and strings, especially useful for
date/time string formats.

 Examples:

o Convert string '123' to integer: CONVERT(int, '123')

o Convert date to formatted string using style number:

 CONVERT(varchar, order_date, 6) outputs dd mon yy (e.g., 25 Apr 21)

 Style 112 outputs YYYYMMDD without separators.

 When style is omitted, a default format (style 0) is used.

[20:42]

CONVERT() Casting Examples:

Example Use Result

Syntax Example Notes
Case Type

Converts
String to Integer CONVERT(int, '123') int numeric string to
int

CONVERT(date, '2025-04- Converts string

String to Date date
25') to date type

DateTime to CONVERT(date, Strips time

date
Date creation_time) FROM portion from
 Example Use Result
Syntax Example Notes
Case Type

sales_orders datetime

Returns string
DateTime to
CONVERT(varchar, formatted
Formatted varchar
creation_time, 32) as MM-DD-
String (US)
YYYY

DateTime to Returns string

CONVERT(varchar,
Formatted varchar formatted as DD-
creation_time, 34)
String (EU) MM-YYYY

[24:17]

Culture and Style Variations with CONVERT() and FORMAT()

 SQL Server supports many culture codes that influence how date and numbers are
formatted.

 Example cultures: Japan, Korea, France, Germany, Arabic, Russian, and more.

 A prepared query is available to explore formatting differences by culture, displaying how

numbers and dates vary by locale.

 This functionality assists in internationalization and localization efforts in data presentation.

[25:15]

CAST() Function - Syntax and Usage

 Syntax:

sqlCopy

CAST(value AS data_type)

 Converts value explicitly to the specified data_type.

 Key difference from CONVERT: Does not include support for formatting or styles; strictly for
type conversion only.

 Examples:

o Convert string to integer: CAST('123' AS int)

o Convert string to date: CAST('2025-04-25' AS date)

o Convert datetime to date (removes time part):

sqlCopy

SELECT CAST(creation_time AS date) FROM sales_orders

 [29:15]

Side-by-Side Comparison of CAST, CONVERT, and FORMAT Functions

Supports Supports
Function Notes
Casting Formatting

Simple and straightforward

CAST Yes No
casting only

Yes (dates Can cast data type and format

CONVERT Yes
only) date/time via style codes

Formats date or numbers as

No (only to Yes (dates styled strings; cannot change
FORMAT
string) and numbers) original data type except to
string

 CAST and CONVERT allow conversion between any data types (string, int, date, datetime,
varchar).

 FORMAT only converts any type to a string with desired formats for presentation.

 For number formatting (currency, percent, numeric), user should rely on FORMAT, as
CONVERT does not support number formatting.

 For date/time formatting with style numbers, CONVERT is preferred over CAST.

[30:10]

Introduction to Date Calculations (Brief Mention)

 The video concludes with a brief mention that the next topic deals with date calculations and
mathematical operations on dates using two SQL functions.

 Details on this topic are Not specified in this video.

Summary

 SQL offers powerful tools for formatting and casting dates, times, and numbers, with
functions like FORMAT(), CAST(), and CONVERT().

 Formatting manipulates appearance without changing data type; casting changes the data
type itself.

 Case sensitivity is critical in format specifiers (e.g., uppercase M for month vs lowercase m for
minutes).

 SQL Server defaults to ISO 8601 date format but supports customizing formats for different
locales and styles.

 FORMAT() is flexible and culture-aware, ideal for displaying data.

 CAST() is straightforward for type conversion without formatting.

 CONVERT() bridges formatting and casting specifically for date/time, with a rich set of style
codes.

 Combining these tools, SQL users can efficiently standardize, convert, and present date and
number data for diverse business and reporting needs.

Key Terms and Definitions

Term Definition

Format A symbolic string that indicates how a date/time or number component should
Specifier be displayed. Case sensitive.

Casting Converting a value from one data type to another (e.g., string to integer).

Formatting Changing the display representation of a value without modifying its data type.

ISO 8601 Internationally standardized date format: YYYY-MM-DD

Regional or country-specific format conventions applied in FORMAT() function

Culture
for localization.

Style
Numeric codes passed to CONVERT() to specify date/time string formats.
(CONVERT)

[00:00]
Introduction to the DATEADD Function

 The DATEADD function is used to add or subtract a specific time interval—such as years,
months, or days—to or from a given date.

 Example: Starting with the date August 20th, 2025, adding 3 years results in August 20th,
2028 (only the year changes).

 Adding months works similarly; for example, adding 2 months changes August 20th, 2025, to
October 20th, 2025.

 Adding days works similarly; adding 5 days changes August 20th to August 25th, 2025.

[01:06]

 DATEADD can subtract time intervals too, even though it's named "add."

 For example, subtracting 3 years from August 20th, 2025 yields August 20th, 2022.

 Subtracting 2 months changes August 20th to June 20th, 2025.

 Subtracting 5 days changes August 20th to August 15th, 2025.

 Key insight: DATEADD manipulates the year, month, and day components of a date by
adding or subtracting the specified interval seamlessly.
 [01:36]
DATEADD Syntax Breakdown

 DATEADD requires three parameters:

1. Part (datepart): Specifies which part to manipulate (e.g., year, month, day).

2. Interval: Specifies how much to add or subtract (integer, positive for add, negative for
subtract).

3. Date: The original date value to manipulate.

 Example: DATEADD(year, 2, OrderDate) adds 2 years to each OrderDate.

 Whether the interval is positive or negative controls if the function adds or subtracts time.

[03:03]
Practical Examples Using DATEADD on Order Dates

 Adding 2 years to each order date consistently produces new values 2 years later.

 Adding 3 months shifts dates forward by 3 months, e.g., January becomes April, February
becomes May.

 Subtracting 10 days affects the day part accordingly, e.g., an order from January 15th
becomes January 5th.

[04:38]
Summary of DATEADD Utility

 It is straightforward to add or subtract years, months, and days using DATEADD.

 This function provides a powerful way to manipulate dates flexibly in SQL.

[04:57]
Introduction to DATEDIFF Function

 Unlike DATEADD, DATEDIFF calculates the difference between two dates.

 It outputs the number of years, months, or days between two dates, depending on the
specified part.

 Example dates: Order Date = August 20th, 2025; Shipping Date = February 1st, 2026.

[05:16]

 Calculating years (DATEDIFF(year, OrderDate, ShipDate)) yields 1 year difference.

 Calculating months returns 3 months difference (from August 2025 to February 2026).

 Calculating days returns 185 days difference (exact count).

 Key insight: DATEDIFF provides a numeric difference in the specified unit, simplifying
calculating durations or intervals.

[06:14]
DATEDIFF Syntax Details

 Parameters:

1. Part (datepart): Year, month, day, etc.

 2. Start Date: The earlier date in the calculation.

3. End Date: The later date.

 Example: DATEDIFF(year, OrderDate, ShipDate) returns how many full years have passed.

 This function assumes chronological order—start date should be earlier than end date.

[07:13]
Task: Calculating Employees’ Age Using DATEDIFF and GETDATE

 Employee age is calculated as the number of years between the birthdate and the current
date (using GETDATE() to get the current date).

 Example query:

sqlCopy

SELECT EmployeeID, BirthDate, DATEDIFF(year, BirthDate, GETDATE()) AS Age

FROM Employees;

 This provides ages accurate to the current system date.

[09:07]
Task: Calculating Average Shipping Duration in Days per Month

 Select order ID, order date, and ship date from the sales orders table.

 Shipping duration is computed as the difference in days between order date and ship date
using:

sqlCopy

DATEDIFF(day, OrderDate, ShipDate) AS DaysToShip

 For example, Order #1 shipped 4 days after ordering, Order #3 took 15 days.

 To find the average shipping duration per month, aggregate by month (extracted from the
order date), then calculate average days:

sqlCopy

SELECT MONTH(OrderDate) AS OrderMonth, AVG(DATEDIFF(day, OrderDate, ShipDate))

AS AvgShipDuration

FROM SalesOrders

GROUP BY MONTH(OrderDate);

 Result summary:

Month Avg Shipping Duration (days)

January ~7

February ~7

March ~5
 Key insight: DATEDIFF is powerful for deriving time-based analytics like shipping durations.

[11:48]
Task: Calculate Number of Days Between Each Order and Previous Order

 The goal is to find the gap in days between consecutive orders.

 Requires identifying the previous order date for each order using the LAG window function:

sqlCopy

SELECT OrderID, OrderDate AS CurrentOrderDate,

LAG(OrderDate) OVER (ORDER BY OrderDate) AS PreviousOrderDate

FROM SalesOrders;

 After retrieving previous order dates, the difference in days is computed with DATEDIFF:

sqlCopy

DATEDIFF(day, PreviousOrderDate, CurrentOrderDate) AS DaysBetweenOrders

 Example results:

OrderID CurrentOrderDate PreviousOrderDate DaysBetween

1 2025-01-01 NULL NULL

2 2025-01-05 2025-01-01 4

3 2025-01-10 2025-01-05 5

 Key insight: Combining window functions with date functions yields powerful time-gap
analysis.

[14:57]
Introduction to ISDATE Function – Date Validation

 The ISDATE function checks if a given string or value is a valid date.

 Returns:

o 1 if the value is a valid date.

o 0 if the value is not a valid date.

 Usage example:

sqlCopy

SELECT ISDATE('2025-08-20') AS DateCheck; -- returns 1

SELECT ISDATE('123') AS DateCheck; -- returns 0

[16:55]
ISDATE Format Limitations

 ISDATE only recognizes standard date formats; non-standard formats like '20-08-2025'
(day-month-year) return 0.
 It can recognize year-only formats such as '2025' as a valid date (assumed as January 1st,
2025).

 However, passing only months or invalid standalone values returns 0.

[18:22]
Use Case: Handling Data Quality Issues with Date Strings

 Converting string values to date can cause errors if some values are invalid.

 Attempting a direct cast:

sqlCopy

CAST(OrderDateString AS DATE)

fails if any string cannot be converted.

 To avoid errors, use ISDATE to filter or conditionally cast only valid dates:

sqlCopy

SELECT OrderDateString,

CASE WHEN ISDATE(OrderDateString) = 1 THEN CAST(OrderDateString AS DATE)

ELSE NULL END AS CleanOrderDate

FROM Orders;

 This approach highlights data quality problems (invalid dates appear as NULL), enabling
easier data cleaning.

[21:20]
Filtering and Identifying Invalid Date Entries

 Using ISDATE in a WHERE clause helps to filter out invalid date strings:

sqlCopy

SELECT *

FROM Orders

WHERE ISDATE(OrderDateString) = 0;

 This is essential for data validation and cleansing in large datasets.

[22:18]
Summary of Covered Date and Time Functions in SQL

 Covered 13 date/time functions including:

o Functions to extract date parts (year, month, day).

o Functions to convert and format dates.

o DATEADD for adding or subtracting intervals (years, months, days).

o DATEDIFF for measuring intervals between two dates.

o ISDATE for validating date strings.

 These functions enable comprehensive date manipulations, calculations, validations, and
aggregations critical for data analysis and reporting.

[23:16]
Conclusion and Next Steps

 With a clear understanding of date/time functions, the viewer is prepared to handle common
date manipulations in SQL effectively.

 The next tutorial will focus on subqueries for complex SQL queries.

 Call to action: Viewers are encouraged to like, subscribe, share, and comment to support the
content creation.