[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 Input Output Conceptual

Example
Type Description Description Description

Single-row One single value One single LEFT('Maria',2) → 'M 1 value in → 1 value
Functions (one row) value (one row) a' out

Multiple values in →
Multi-row Multiple values One single SUM(30,10,20,40) →
1 summarized
Functions (multiple rows) summary value 100
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 n characters from the left (start) of the string.
Example:
Input: "Maria", LEFT(string, 2) → "Ma"

 RIGHT(string, n)
Extracts the last n 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): M =1, a=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 CONCAT(string1, Can include separators (spaces,

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

Convert all
UPPER characters to UPPER(string) Useful for data standardization
uppercase

Convert all
LOWER characters to LOWER(string) Useful for data standardization
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,
replacing with empty string
one new_substring)
Function Purpose Parameters Notes

Count number of
Includes letters, digits, spaces,
LENGTH characters in a LENGTH(string)
symbols
string

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

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

Extract characters
SUBSTRIN SUBSTRING(string,
from specific Flexible extraction from middle
G start_pos, length)
position

[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 Inp Outp

Explanation
Places ut ut

3.51 Second digit after decimal is 1, no

1 3.5
6 rounding up

3.51 First digit after decimal is 5, rounds

0 4
6 up to 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.
The instructor highlights the practical usefulness of this function to transform and

Functi Purpo Out

Example Notes
on se put

Round
Rounds
numer
up or
ic
down
values
based on
ROUN to ROUND(3.5
3.52 digit
D specifi 16, 2)
immedia
ed
tely after
decim
rounding
al
position
places

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

Rounds
ROUND(3.5 to
4
16, 0) nearest
integer

Conve
rts a Converts
numb negative
er to s to
its positive,
ABS ABS(-10) 10
absolu leaves
te positives
(positi unchang
ve) ed
value

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,
TIMESTAMP/ Date + Time (including
MySQL, SQL Server
DATETIME 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:

Descr
Cate
Functions Included iptio
gory
n

Extrac
t
parts
Part
DAY(), MONTH(), YEAR(), DATEPART(), DATENAME(), of
Extra
DATE_TRUNC(), EOMONTH() dates
ction
or
trunc
ate

Conve
Form rt or
atting forma
& FORMAT(), CONVERT(), CAST() t
Casti date/t
ng ime
types

Add/
subtra
Calcul ct or
DATEADD(), DATEDIFF()
ations find
interv
als

Valida
te if a
Valida date
ISDATE()
tion string
is
valid

[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 (#
Granularity Explanation
of 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

DAY(), MONTH(), YEAR(), DATE Numeric parts extracted

Integer
PART() from dates

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

DATETIME / Date truncated to specified

DATE_TRUNC()
TIMESTAMP part, includes time

Last day of the month, date

EOMONTH() 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

 DATEPART() O DATENAME() O DATE_TRUNC() O
Part
utput utput utput

Integer (e.g., String (e.g., Truncates date to

year
2024) "2024") year

Truncates date to
quarter Not
quarte String (same as specified (function
Integer (1-4)
r integer) typically truncates
to month level in
practice)

String (e.g., Truncates date to

month Integer (1-12)
"August") month

String (same as Truncates date to

day Integer (1-31)
integer) day

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

weekd String (e.g.,

Integer (1-7) Not applicable
ay "Monday")

String (same as Truncates time to

hour Integer (0-23)
integer) hour

String (same as Truncates time to

minute Integer (0-59)
integer) minute

secon String (same as Truncates time to

Integer (0-59)
d 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

 Format Output Example Description

 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
CONVERT(int, '123') int numeric string
Integer
to int

CONVERT(date, '2025- Converts string

String to Date date
04-25') to date type

CONVERT(date, Strips time

DateTime to
creation_time) FROM date portion from
Date
sales_orders datetime

DateTime to Returns string

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

DateTime to Returns string

CONVERT(varchar,
Formatted varchar formatted
creation_time, 34)
String (EU) as DD-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

Functio Supports Supports

Notes
n Casting Formatting

Simple and straightforward

CAST Yes No
casting only

Can cast data type and

CONVER Yes (dates
Yes format date/time via style
T only)
codes

Formats date or numbers as

Yes (dates
No (only styled strings; cannot change
FORMAT and
to string) original data type except to
numbers)
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

Specifier component should be displayed. Case sensitive.

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

Casting
integer).

Changing the display representation of a value without modifying its

Formatting
data type.

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

Regional or country-specific format conventions applied

Culture
in FORMAT() function for localization.
 Term Definition

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

(CONVERT) formats.

[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:

Order CurrentOrder PreviousOrder DaysBetweenO

ID Date Date ders

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.