SQL Server System Functions

The SQL Server system functions offer a comprehensive set of built-in functions that help in data manipulation and analysis.

Let us discuss about some very important SQL Server system functions in this article. We will talk about how to perform the tasks such as data type conversions, handling the null values, extracting specific parts of date or time values, calculating the string lengths, performing the aggregate calculations, and extracting the substrings. After understanding these SQL Server system functions, it will help us to optimize the database operations and extract useful information from our data.

Create a Sample Table for Demonstration

Let us create a sample table named as “EmP” and insert it with some values to understand the practical usage of the SQL Server system functions. Let’s first create a sample table called “EmP” with columns such as BatchID, Fname, LName, BirthDate, and Salary.



BatchID INT,

Fname VARCHAR(50),

LName VARCHAR(50),

BirthDate DATE,

Salary DECIMAL(10, 2)



INSERT INTO EmP (BatchID, Fname, LName, BirthDate, Salary)


(1, 'Ram', 'Nath', '1985-07-12', 50000),

(2, 'Moddhu', 'Das', '1990-02-15', 60000),

(3, 'Alex', 'Rozario', '1988-09-20', 55000),

(4, 'Tanmoy', 'Dutta', '1995-05-10', 45000),

(5, 'Piu', 'Saha', '1992-12-01', 70000);


BatchID Fname LName BirthDate Salary

1 Ram Nath 1985-07-12 50000.00

2 Moddhu Das 1990-02-15 60000.00

3 Alex Rozario 1988-09-20 55000.00

4 Tanmoy Dutta 1995-05-10 45000.00

5 Piu Saha 1992-12-01 70000.00

Types of System Functions in SQL Server

SQL Server provides an extensive library of system functions that serve to diverse data manipulation and analysis requirements. These functions are built-in features provided by the SQL Server and are available for use. Let us discuss about some very important SQL Server system functions that are used frequently.

1. CAST and CONVERT Functions

The CAST and CONVERT functions enable the data type conversion.

-- CAST function

SELECT CAST (Salary AS INT) AS ConvertedSalary

FROM Employees;


-- CONVERT function

SELECT CONVERT (VARCHAR, BirthDate, 106) AS FormattedBirthDate

FROM Employees;


** CAST function output**







** CONVERT function output**


12 Jul 1985

15 Feb 1990

20 Sep 1988

10 May 1995

01 Dec 1992

Here in the T-SQL query, the CAST function converts the “Salary” column to an integer. The CONVERT function formats the “BirthDate” column as a string using the format 106 (“dd month yyyy”).

2. ISNULL Function

This function replaces the NULL values with a specified value to ensure the consistent handling of nulls in query results.

SELECT ISNULL (Lname, 'N/A') AS Lname

FROM Employees;








Here in the T-SQL query, the ISNULL function checks the “ProductName” column for NULL values. If a NULL value is found, it replaces it with “N/A”, providing a more meaningful output.

3. DATEPART and DATENAME Functions

These functions extract specific parts of a date or time value such as year, month, or day.

Suppose we need to extract our employee “birth year” and their “birth month”. So, we need to write the following T-SQL query:

SELECT DATEPART (YEAR, BirthDate) AS BirthYear, DATENAME (MONTH, BirthDate) AS BirthMonth

FROM Employees;


BirthYear BirthMonth

1985 July

1990 February

1988 September

1995 May

1992 December

Here in the T-SQL query, the DATEPART function extracts the year from the “BirthDate” column, while the “DATENAME” function extracts the month name. This allows us for easy analysis and grouping of employees’ birthdates.

4. LEN Function

The LEN function in SQL server calculates the length of a string.

SELECT Fname, LEN (Fname) AS FnameLength

FROM Employees;


Fname FnameLength

Ram 3

Moddhu 6

Alex 4

Tanmoy 6

Piu 3

Here in the T-SQL query, the LEN function calculates the length of the “Fname” column, providing the number of characters in each employee’s first name.

5. Aggregate Functions

Some useful aggregate functions in SQL Server are AVG, SUM, COUNT, MIN, and MAX. Let us discuss about each using the T-SQL query.

SELECT AVG (Salary) AS AverageSalary, SUM (Salary) AS TotalSalary,

COUNT (*) AS TotalEmployees, MIN (Salary) AS MinSalary, MAX (Salary) AS MaxSalary

FROM Employees;


AverageSalary TotalSalary TotalEmployees MinSalary MaxSalary

56000.000000 280000.00 5 45000.00 70000.00

Here in the T-SQL query, these aggregate functions calculate the various statistics on the “Salary” column including the average salary, total salary, total number of employees, minimum salary, and maximum salary.

6. SUBSTRING Function

The SUBSTRING function in SQL Server extracts only a portion of a string. Here, we extract only the first three letters from the “Fname” column.

SELECT SUBSTRING(Fname, 1, 3) AS ShortFname

FROM Employees;








Here in the T-SQL query, the SUBSTRING function extracts the first three characters from the “Fname” column, providing the reduced versions of each employee’s first name.

With the “Employees” table and these system methods in MS SQL, we may effectively manipulate, analyse, and modify the data. It makes the database maintenance and query operations more efficient. These features expand the SQL Server’s capabilities and versatility, making it a solid option for applications that rely on data.


The SQL Server system functions play a crucial role in data manipulation and analysis within the Microsoft SQL Server. We can perform various operations using functions such as CAST, CONVERT, ISNULL, DATEPART, DATENAME, LEN, and aggregate. These functions improve the functionality and efficiency of SQL Server, and make it a powerful tool for managing the data effectively.

About the author

Somdeb Nath

Somdeb Nath is a highly accomplished programmer with expertise in C, Java, Python, SQL, HTML, CSS, Android, Linux and JavaScript. He has authored 100+ high-quality articles on programming languages. With strong problem-solving skills and a passion for technology, Somdeb is dedicated to continuous learning. Engage with him on LinkedIn: