Mastering Date and Time Functions in SQL Server

6 min readSep 17, 2024

Syntax and Practical Examples for Effective Date and Time Management

Efficient handling of date and time is crucial in database management and development. SQL Server offers powerful tools for handling, manipulating, and extracting date and time information.

In this article, we will delve into the different date and time functions offered by SQL Server. We will explore how to utilize these functions to effectively manage and work with date and time information.

Date and Time Data Types Comparison

SQL Server offers a range of date and time types with varying levels of precision and range.

Date and Time Functions

In addition to various data types, SQL Server and MySQL deliver a host of date and time functions that enable accurate manipulation and retrieval of date and time details. These functions are crucial for executing complex operations and analyses involving temporal data.

1. GETDATE()

Returns the current date and time of the SQL Server instance.

Data Type: DATETIME

SELECT GETDATE();

2. SYSDATETIME()

Returns the current date and time with higher precision than GETDATE().
Data Type: DATETIME2

SELECT SYSDATETIME();

3. CURRENT_TIMESTAMP

Returns the current date and time.
Data Type: DATETIME
Usage: Equivalent to GETDATE(), used in the same contexts.

SELECT CURRENT_TIMESTAMP;

4. SYSUTCDATETIME()

Returns the current date and time in UTC with high precision.
Data Type: DATETIME2
Usage: Useful for applications needing UTC date and time.

SELECT SYSUTCDATETIME();

5. DAY(date)

Returns the day of the month from the provided date i.e. the day part of a date.
Syntax: DAY(date)

SELECT DAY('2024–09–16');

It will return 16

6. MONTH(date)

Extracts the month part of a date.
Syntax: MONTH(date)

SELECT MONTH('2024–09–16');

It will return 9.

7. YEAR(date):

Extracts the year part of a date.
Syntax: YEAR(date)

SELECT YEAR('2024–09–16');

It will return 2024.

8. DATENAME(datepart, date)

The DATENAME function is used to extract a specified part of a date and return it as a string (nvarchar).
This function allows to retrieve more descriptive information about a date, such as the full name of the day of the week or the month.
Syntax: DATENAME(datepart, date)

datepart: The part of the date to return (e.g., year, month, day, etc.).

date: The date value from which to extract the date part.

Examples:

a) Day of the Week:

SELECT DATENAME(weekday, '2024–09–16');

Explanation: Extracts the name of the weekday for the given date. the code above will return ‘Monday’.

b) Month:

SELECT DATENAME(month, '2024–09–16');

Explanation: Extracts the name of the month for the given date. The above code will return ‘September’.

c) Year:

SELECT DATENAME(year, '2024–09–16');

-Explanation: Extracts the year from the given date as a string. The above code will return ‘2024’.

Common Date Parts (datepart) Options:

Year: year, yy, or yyyy
Quarter of the year: quarter, qq, or q
Month part: month, mm, or m
Day part: day or dd or d
Day of year: dayofyear, dy, or y
Day of the week: weekday or dw
Week: week or wk or ww
Hour part: hour or hh
Minute part: minute or mi or n
Second part: second or ss or s

9. DATEPART(datepart, date)

The DATEPART function is used to extract a specific part of a date or time value and returns an integer value.
In SQL Server, the DATEPART function has the following syntax: DATEPART(datepart, date),
where, datepart specifies the part of the date to return (e.g., year, month, day, hour).
and date is the date or time expression.

Example: SELECT DATEPART(year, GETDATE()) AS Year;

This will return the current year.

10. DATEADD(datepart, number, date)

The DATEADD function in SQL Server and its equivalent in MySQL are used to add a specified time interval to a date or time value.
Syntax: DATEADD(datepart, number, date)

Where, datepart: The part of the date to add (e.g., year, month, day).

number: The value to add to the specified datepart. Can be positive or negative.

date: The date or time value to which the number will be added.

Example: Add 10 days to the current date

SELECT DATEADD(day, 10, GETDATE()) AS NewDate;

11. DATEDIFF(datepart, startdate, enddate)

DATEDIFF function calculates the difference between two dates and returns the result in the specified date part.
Syntax: DATEDIFF(datepart, startdate, enddate)

Where, datepart: The part of the date to return the difference in (e.g., year, month, day).

startdate: The starting date.

enddate: The ending date.

Example: Calculate the number of days between two dates

SELECT DATEDIFF(day, '2024–01–01', '2024–12–31') AS DaysDifference;

12. ISDATE(expression)

This function is particularly useful for validating data before attempting conversions or storing values in date/time columns.
Checks if an expression is a valid date/time value.
Syntax: ISDATE(expression)
Returns 1 if the expression is a valid date/time value and 0 if the expression is not a valid date/time value.
Examples:

SELECT ISDATE('2024–09–16');

This will returns 1.

Explanation: Checks if ‘2024–09–16’ is a valid date, which it is.

Examples of SQL Queries with Date Functions:

Write a SQL query to get the employee’s name, birth date, and hire date for employees who were born on June 1, 1993.

-- Create Table

CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATETIME            NOT NULL,
    full_name  NVARCHAR(50)     NOT NULL,
    gender      CHAR(1)   NOT NULL,    
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
);

-- Insert Data

INSERT INTO employees (emp_no, birth_date, full_name, gender, hire_date)
VALUES
    (2201, '1980-01-15 08:45:00', 'Michael Thompson', 'M', '2024-09-15'),
    (1502, '1985-07-22 14:30:00', 'Sophia Martinez', 'F', '2022-08-15'),
    (1003, '1990-11-30 19:15:00', 'Liam Anderson', 'M', '2018-06-10'),
    (2194, '1988-02-20 03:00:00', 'Emma Robinson', 'F', '2024-09-12'),
    (1485, '1993-06-01 12:00:00', 'Noah Harris', 'M', '2021-04-25'),
    (1496, '1983-09-25 07:25:00', 'Olivia Walker', 'F', '2021-11-05'),
    (1607, '1991-10-05 22:10:00', 'Ethan Scott', 'M', '2023-07-16'),
    (1208, '1987-03-17 16:45:00', 'Ava Lewis', 'F', '2019-09-12'),
    (1449, '1989-11-30 05:50:00', 'James Young', 'M', '2020-02-22'),
    (1489, '1993-06-01 11:30:00', 'Geeta Biswas', 'F', '2021-05-30');

select 
  full_name as name
  , CAST(birth_date as DATE) as DateOfBirth
  , hire_date
from employees
where CAST(birth_date as DATE) = '1993–06–01' ;

2. Write a SQL query to get the full name and hire date of employees who were hired between January 1, 2021, and December 31, 2022.

SELECT 
  full_name
  , hire_date
FROM employees
WHERE hire_date BETWEEN '2021–01–01' AND '2022–12–31';

3. Write a SQL query to get the full name, gender, and date of birth of employees who were born on November 30, regardless of the year.

SELECT 
  full_name
  , gender
  , CAST(birth_date AS DATE) AS DateOfBirth
FROM employees
WHERE DAY(birth_date) = 30 AND MONTH(birth_date) =11;

4. Write a SQL query to get the full name, gender, and date of birth of employees who were hired in the year 2021.

SELECT   
  full_name
  , gender
  , CAST(birth_date AS DATE) AS DateOfBirth
  , hire_date
FROM employees
WHERE YEAR(hire_date) = 2021;

5. Write a SQL query to get the full name, gender, and date of birth of employees who were hired yesterday.

SELECT 
  full_name
  , gender
  , CAST(birth_date AS DATE) AS DateOfBirth
  , hire_date
FROM employees
WHERE hire_date = DATEADD(day, -1, CAST(GETDATE() AS DATE));

6. Write a SQL query to get the full name, gender, and date of birth of employees who were hired in the last 7 days.

SELECT 
  full_name
  , gender
  , CAST(birth_date AS DATE) AS DateOfBirth
  , hire_date
FROM employees
WHERE hire_date >= DATEADD(day, -7, CAST(GETDATE() AS DATE));

7. Write a SQL query to determine the number of business days between two dates, including holidays and holidays that fall on weekends.

-- Create holidays table

CREATE TABLE holidays (
    id INT PRIMARY KEY,
    holiday_name VARCHAR(100),
    holiday_date DATE
);

-- Insert Data into holidays

INSERT INTO holidays (id, holiday_name, holiday_date) VALUES
(1, 'Thanksgiving', '2024-10-14'),
(2, 'Remembrance Day', '2024-11-11'),
(3, 'General Holiday', '2024-11-16'),
(4, 'Christmas Day', '2024-12-25'),
(5, 'Boxing Day', '2024-12-26');

-- Create data_ranges table

CREATE TABLE date_ranges (
    id INT PRIMARY KEY,
    start_date DATE,
    end_date DATE
);

-- Insert data into data_ranges

INSERT INTO date_ranges (id, start_date, end_date) VALUES
(1, '2024-10-10', '2024-10-28'),
(2, '2024-11-08', '2024-11-22'),
(3, '2024-12-08', '2024-12-31');

WITH cte AS (
    SELECT dr.id as range_id
           , start_date
           , end_date 
           , COUNT(holiday_date) AS no_of_holidays
    FROM date_ranges dr
    LEFT JOIN holidays h
    ON h.holiday_date BETWEEN dr.start_date AND dr.end_date
    AND  DATENAME(weekday, holiday_date) NOT IN ('Saturday', 'Sunday')
    GROUP BY dr.id, start_date, end_date)
SELECT  *
    , DATEDIFF(day, start_date, end_date) + 1 AS actual_days
    , DATEDIFF(day, start_date, end_date) + 1 
       - 2 * DATEDIFF(week, start_date, end_date) - no_of_holidays
    AS business_days 
FROM  cte;

In conclusion, these date and time functions in SQL offer enhanced capabilities for manipulating, calculating, and formatting date and time data to meet your specific requirements. Therefore, understanding and utilizing date and time functions in SQL Server and MySQL is essential for efficient database operations and accurate data handling.

Mastering Date and Time Functions in SQL Server

Date and Time Data Types Comparison

Date and Time Functions

Examples of SQL Queries with Date Functions:

Written by Suparna Chowdhury

No responses yet