Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

SQL Server Date & Time Functions

Date and Time Functions in SQL Server are used to work with dates, times, extract date parts, calculate date differences and manipulate date values.

What are Date & Time Functions?

Date and Time Functions are built-in SQL Server functions used to retrieve, manipulate and calculate date and time values.

These functions are commonly used in applications for storing registration dates, joining dates, order dates, payment dates, birthdays, deadlines and timestamps.

Example:

Suppose an Employee table contains an employee's joining date. Date functions can be used to find the year of joining, calculate the employee's experience or find the number of days between two dates.

Common Date & Time Functions

Function Purpose Example
GETDATE() Returns the current system date and time. GETDATE()
SYSDATETIME() Returns the current system date and time with higher precision. SYSDATETIME()
GETUTCDATE() Returns the current UTC date and time. GETUTCDATE()
DATEPART() Returns a specific part of a date. DATEPART(YEAR, GETDATE())
YEAR() Returns the year from a date. YEAR(GETDATE())
MONTH() Returns the month from a date. MONTH(GETDATE())
DAY() Returns the day from a date. DAY(GETDATE())
DATEADD() Adds a specified time interval to a date. DATEADD(DAY, 10, GETDATE())
DATEDIFF() Calculates the difference between two dates. DATEDIFF(DAY, '2025-01-01', GETDATE())
EOMONTH() Returns the last day of a month. EOMONTH(GETDATE())

1. GETDATE() Function

The GETDATE() function returns the current system date and time of the SQL Server.

Syntax
GETDATE()
Example
SELECT GETDATE();
Output:
The current date and time of the SQL Server.

The exact output depends on the system date and time when the query is executed.

2. SYSDATETIME() Function

The SYSDATETIME() function returns the current system date and time with higher fractional seconds precision than GETDATE().

Syntax
SYSDATETIME()
Example
SELECT SYSDATETIME();

3. GETUTCDATE() Function

The GETUTCDATE() function returns the current UTC date and time.

Syntax
GETUTCDATE()
Example
SELECT GETUTCDATE();
UTC stands for Coordinated Universal Time.

4. DATEPART() Function

The DATEPART() function returns a specific part of a date such as year, month, day, hour or minute.

Syntax
DATEPART(datepart, date)
Example
SELECT DATEPART(YEAR, GETDATE());
Output: Current year
More Examples
SELECT DATEPART(MONTH, GETDATE());

SELECT DATEPART(DAY, GETDATE());

SELECT DATEPART(HOUR, GETDATE());

5. YEAR() Function

The YEAR() function extracts the year from a date value.

Syntax
YEAR(date)
Example
SELECT YEAR('2026-10-05');
Output: 2026

6. MONTH() Function

The MONTH() function returns the month number from a date.

Syntax
MONTH(date)
Example
SELECT MONTH('2026-10-05');
Output: 10

7. DAY() Function

The DAY() function returns the day number from a date.

Syntax
DAY(date)
Example
SELECT DAY('2026-10-05');
Output: 5

8. DATEADD() Function

The DATEADD() function adds a specified number of date or time units to a date.

Syntax
DATEADD(datepart, number, date)
Example
SELECT DATEADD(DAY, 10, '2026-10-05');
Output: 2026-10-15

Here, 10 days are added to the given date.

More Examples
SELECT DATEADD(MONTH, 2, '2026-10-05');

SELECT DATEADD(YEAR, 1, '2026-10-05');

SELECT DATEADD(DAY, -5, '2026-10-05');

9. DATEDIFF() Function

The DATEDIFF() function returns the difference between two dates based on the specified date part.

Syntax
DATEDIFF(datepart, startdate, enddate)
Example
SELECT DATEDIFF(DAY, '2026-10-01', '2026-10-05');
Output: 4

There are four days between October 1 and October 5.

Calculate Years
SELECT DATEDIFF(YEAR, '2020-01-01', '2026-01-01');
Output: 6

10. EOMONTH() Function

The EOMONTH() function returns the last day of the month for a specified date.

Syntax
EOMONTH(start_date)
Example
SELECT EOMONTH('2026-10-05');
Output: 2026-10-31

October has 31 days, so the last day of the month is October 31.

Practical Example with Employee Table

Suppose we have an Employees table:

CREATE TABLE Employees
(
    EmployeeId INT,
    EmployeeName VARCHAR(100),
    JoiningDate DATE
);

Insert sample records:

INSERT INTO Employees
VALUES
(101, 'Rahul Sharma', '2020-01-15'),
(102, 'Priya Patil', '2022-06-10'),
(103, 'Amit Kumar', '2024-03-20');
Find Joining Year
SELECT
    EmployeeName,
    JoiningDate,
    YEAR(JoiningDate) AS JoiningYear
FROM Employees;
Find Joining Month
SELECT
    EmployeeName,
    MONTH(JoiningDate) AS JoiningMonth
FROM Employees;
Find Number of Days Since Joining
SELECT
    EmployeeName,
    DATEDIFF(DAY, JoiningDate, GETDATE()) AS DaysCompleted
FROM Employees;
Add 1 Year to Joining Date
SELECT
    EmployeeName,
    DATEADD(YEAR, 1, JoiningDate) AS NextYearDate
FROM Employees;

DATEADD() vs DATEDIFF()

Function Purpose Example
DATEADD() Adds or subtracts a date interval. DATEADD(DAY, 10, GETDATE())
DATEDIFF() Finds the difference between two dates. DATEDIFF(DAY, StartDate, EndDate)

When Do We Use Date & Time Functions?

  • To get the current date and time.
  • To extract year, month and day from a date.
  • To calculate the difference between two dates.
  • To add or subtract days, months or years.
  • To calculate employee experience.
  • To calculate order and delivery dates.
  • To find the beginning or end of a date period.

Important Points

  • GETDATE() returns the current SQL Server date and time.
  • YEAR(), MONTH() and DAY() extract individual date components.
  • DATEPART() can extract different parts of a date.
  • DATEADD() adds or subtracts a date interval.
  • DATEDIFF() calculates the difference between dates.
  • EOMONTH() returns the last day of a month.
  • Date functions are widely used in real-world database applications.

Summary

SQL Server Date and Time Functions are used to work with date and time values. In this topic, we learned GETDATE(), SYSDATETIME(), GETUTCDATE(), DATEPART(), YEAR(), MONTH(), DAY(), DATEADD(), DATEDIFF() and EOMONTH(). These functions are extremely useful for employee records, orders, payments, reports, scheduling and date-based calculations.