SQL Server System Functions: Aggregate, String, Math, Date

SELECT MIN(COLUMN_NAME) FROM TABLE_NAME;
SELECT MAX(COLUMN_NAME) FROM TABLE_NAME;
SELECT COUNT(COLUMN_NAME) FROM TABLE_NAME;
SELECT SUM(COLUMN_NAME) FROM TABLE_NAME;
SELECT AVG(COLUMN_NAME) FROM TABLE_NAME;

SELECT LEN(COLUMN_NAME) FROM TABLE_NAME
SELECT LTRIM(COLUMN_NAME) FROM TABLE_NAME
SELECT RTRIM(COLUMN_NAME) FROM TABLE_NAME
SELECT TRIM(COLUMN_NAME) FROM TABLE_NAME
SELECT LOWER(COLUMN_NAME) FROM TABLE_NAME
SELECT UPPER(COLUMN_NAME) FROM TABLE_NAME
SELECT SUBSTRING(COLUMN_NAME, 2, 2) FROM TABLE_NAME
SELECT REVERSE(COLUMN_NAME) FROM TABLE_NAME
SELECT REPLACE(COLUMN_NAME, 'X', 'Y') FROM TABLE_NAME
SELECT CHARINDEX('A', COLUMN_NAME) FROM TABLE_NAME

Mathematical Function

SQL Server provides a variety of mathematical functions. The mathematical function is for returning numeric expressions. The following items are commonly used in mathematical functions (CEILING, FLOOR, ROUND).

CEILING

Returns the smallest integer value that is >=X to it.

Example

X=12.320

SELECT CEILING(X) FROM TB_NAME  

Output

X=13

FLOOR

Returns the largest integer value that is <=X to it.

Example

X=12.230

SELECT FLOOR(X) FROM TB_NAME  

Output

X=12

ROUND

Rounds a number to a specified number of decimal places.

Example

X=12.230

SELECT ROUND(X,2) FROM TB_NAME  

Output

X=12.23

DateTime

The DateTime function returns the DateTime. The following items are commonly used in DateTime functions (GETDATE, DATEADD, DATEDIFF, DATEPART, DATENAME, GETUTCDATE, SYSUTCDATETIME).

GETDATE

Return the current database system datetime.

SELECT GETDATE();  

DATEADD

The datetime function adds a date and time or subtracts the date/time and returns the date.

SELECT DATEADD(MONTH, 10, GETDATE()); -- Add Month
SELECT DATEADD(DAY, 10, GETDATE()); -- Add day
SELECT DATEADD(YEAR, 10, GETDATE()); -- Add year
SELECT DATEADD(MONTH, -10, GETDATE()); -- Sub Month
SELECT DATEADD(DAY, -10, GETDATE()); -- Sub day
SELECT DATEADD(YEAR, -10, GETDATE()); -- Sub year

DATEDIFF

The datediff function displays the date and time between two dates.

SELECT DATEDIFF(MONTH,DATEADD(MONTH,-10,GETDATE()),GETDATE());--Diff b/w Month  
SELECT DATEDIFF(DAY,DATEADD(MONTH,-10,GETDATE()),GETDATE());--Diff b/w day  
SELECT DATEDIFF(YEAR,DATEADD(MONTH,-10,GETDATE()),GETDATE());--Diff b/w Year  

DEPART

The datepart function returns a specified part of a date as an integer.

SELECT DATEPART(MONTH, GETDATE()); -- output is 11
SELECT DATEPART(DAY, GETDATE()); -- output is 12
SELECT DATEPART(YEAR, GETDATE()); -- output is 2019

DATENAME

DateName function returns a specified part of a date as a string.

SELECT DATENAME(MONTH,GETDATE());--output is november  
SELECT DATENAME(DAY,GETDATE());--output is 12  
SELECT DATENAME(YEAR,GETDATE())--output is 2019  

GETUTCDATE

The getutcdate function returns a current database system UTC date and time.

SELECT GETUTCDATE()  

SYSUTCDATETIME

The sysutcdatetime function returns the date and time of the SQL Server.

SELECT SYSUTCDATETIME()  

Date Conversion

DateConversion returns the date and time in different formats. The following queries return the date and time in different formats in MS SQL Server.

SELECT CONVERT(NVARCHAR, GETDATE(), 100); -- output is Nov 14 2019 12:31AM
SELECT CONVERT(NVARCHAR, GETDATE(), 101); -- output is 11/14/2019
SELECT CONVERT(NVARCHAR, GETDATE(), 102); -- output is 2019.11.14
SELECT CONVERT(NVARCHAR, GETDATE(), 103); -- output is 14/11/2019
SELECT CONVERT(NVARCHAR, GETDATE(), 104); -- output is 14.11.2019
SELECT CONVERT(NVARCHAR, GETDATE(), 105); -- output is 14-11-2019
SELECT CONVERT(NVARCHAR, GETDATE(), 106); -- output is 14 Nov 2019
SELECT CONVERT(NVARCHAR, GETDATE(), 107); -- output is Nov 14, 2019
SELECT CONVERT(NVARCHAR, GETDATE(), 108); -- output is 00:31:46

Summary

In this article, you have had an overview of the system functions. I have written this article focusing on beginners and students.