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.
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();
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();
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());
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');
6. MONTH() Function
The MONTH() function returns the month number from a date.
Syntax
MONTH(date)
Example
SELECT MONTH('2026-10-05');
7. DAY() Function
The DAY() function returns the day number from a date.
Syntax
DAY(date)
Example
SELECT DAY('2026-10-05');
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');
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');
There are four days between October 1 and October 5.
Calculate Years
SELECT DATEDIFF(YEAR, '2020-01-01', '2026-01-01');
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');
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.