BUILT-IN FUNCTIONS

Instructor:

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 1

Learning Goals
Understand about the built-in functions in SQL Server
By the end of this lecture
students should be able to:
Recognize how to use built-in functions to perform
operations on data

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 2

Table of contents
◊ Conversion Functions
◊ Date and Time Functions
◊ String Functions

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 3

Section1

CONVERSION FUNCTIONS

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 4

CAST Function
▪ Converts an expression of one data type to another.

Syntax for CAST:

CAST ( expression AS data_type [ ( length ) ] )

▪ The Cast() function is used to convert a data type variable or data from one
data type to another data type.
▪ The Cast() function provides a data type to a dynamic parameter (?) or a NULL
value.

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 5

CONVERT Function (1/3)
▪ When you convert expressions from one type to another, in many cases there
will be a need within a stored procedure or other routine to convert data from a
datetime type to a varchar type.
▪ The Convert function is used for such things. The CONVERT() function can be
used to display date/time data in various formats

Syntax for CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

✓ Style (0 or 100): mon dd yyyy hh:miAM (or PM)

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 6

CONVERT Function (2/3)
Without century With century (yyyy) Standard Input/Output
(yy)
- 0 or 100 Default mon dd yyyy hh:miAM (or PM)
1 101 U.S. mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yyyy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - Mon dd, yy
8 108 - hh:mi:ss
- 9 or 109 Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 7

CONVERT Function (3/3)
Without century With century (yyyy) Standard Input/Output
(yy)
12 112 ISO yymmdd
Yyyymmdd
- 13 or 113 Europe default + dd mon yyyy hh:mi:ss:mmm(24h)
milliseconds
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 ODBC canonical (with yyyy-mm-dd hh:mi:ss.mmm(24h)
milliseconds)
- 126 ISO8601 yyyy-mm-ddThh:mi:ss.mmm (no spaces)
- 127 ISO8601 with time zone Z yyyy-mm-ddThh:mi:ss.mmmZ
(no spaces)
- 130 Hijri dd mon yyyy hh:mi:ss:mmmAM
- 131 Hijri dd/mm/yy hh:mi:ss:mmmAM

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 8

Section2

DATE AND TIME FUNCTIONS

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 9

GETDATE() & DATEPART() Function (1/2)

▪ The GETDATE() function returns the current date and time from the SQL
Server.
▪ The DATEPART() function is used to return a single part of a date/time, such as
year, month, day, hour, minute, etc.
Syntax:
GETDATE()
DATEPART(datepart, date)

▪ Ex : SELECT GETDATE()
SELECT DATEPART(YYYY, GETDATE())
Result :

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 10

GETDATE() & DATEPART Function (2/2)
datepart Abbreviation
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 11

DAY, MONTH,YEAR Function

▪ Returns an integer representing the day/month/year (day of the month) of the

specified date.
Syntax:
DAY(date)
MONTH(date)
YEAR(date)

▪ Ex : SELECT DAY(GETDATE()) AS [Day],

MONTH(GETDATE()) AS [Month],
YEAR(GETDATE()) AS [Year]
Result :

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 12

DATEADD Function
▪ The DATEADD() function adds or subtracts a specified time interval
from a date.
Syntax:
DATEADD(datepart,number,date)

▪ Ex : DECLARE @dt datetime

SET @dt = GETDATE()
SELECT @dt AS CurrentDate
SELECT DATEADD(day, 30, @dt) AS AffterDate
Result :

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 13

DATEDIFF Function
▪ The DATEDIFF() function returns the time between two dates.
Syntax:
DATEDIFF (datepart,startdate,enddate)

▪ Ex:
DECLARE @date1 DATETIME
DECLARE @date2 DATETIME
SET @date1= '2019-04-07 20:12:22.013'
SET @date2= '2020-02-27 22:14:10.013'
SELECT DATEDIFF(month, @date1, @date2) AS 'Month'
Result: 10

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 14

Section3

STRING FUNCTIONS

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 15

RTRIM, LTRIM Function
▪ LTRIM/RTRIM: removes all white spaces from the beginning/ending of the
string.
Syntax:
LTRIM (str)
RTRIM (str)

▪ Ex : SELECT LTRIM(' Sample ');

SELECT RTRIM(' Sample ');
Result :

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 16

SUBSTRING Function
▪ The Substring function in SQL is used to return a portion of string. This function
is called differently in different databases:

Syntax:
SUBSTRING(str, position, length)

▪ Note: : The first position in string is 1

▪ Ex : SELECT SUBSTRING('Bill Gates', 1 ,4)
Result :

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 17

LEN, CHARINDEX, PATINDEX Function

▪ The CHARINDEX and PATINDEX functions return the starting position of a

pattern you specify.
▪ PATINDEX can use wildcard characters, but CHARINDEX cannot
Syntax: LEN(str)
CHARINDEX ( expression1 ,expression2 [ , start_location ] )
PATINDEX ( '%pattern%' , expression )

▪ Ex : SELECT CHARINDEX('bicycle',
'Reflectors are vital safety components of your bicycle.') AS Positions
SELECT PATINDEX ( '%ein%', 'Das ist ein Test') AS Positions
Result:

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 18

Summary
✓ Conversion Functions
 CAST, CONVERT Function

✓ Date and Time Functions

 GETDATE, DATEPART, DAY, MONTH,YEAR, DATEDD, DATEIFF Function

✓ String Functions
 RTRIM, LTRIM, SUBSTRING, LEN, CHARINDEX, PATINDEX

✓ Demo

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 19

Thank you

2/24/2022 09e-BM/DT/FSOFT - ©FPT SOFTWARE – Fresher Academy - Internal Use 20 20