Federal University Gashua
Department of Computer Science
CHAPTER FIVE: Functions
(Single-Row Function)
USMAN SULEIMAN IDRISS
Objectives
Understand the usage of functions in SELECT
statements
Using Number and Text functions
Display current date and time from Access
Performed calculation with Dates
Using Dates and Conversion functions
Change the display of dates, number and text
What is Function?
A function is a type of procedure or routine
that performs a specific task.
A function can receive argument and always
returns a value.
F A
T F
Sorting function
G G
A R
R T
Two Types of SQL Functions
Functions
Single-row Multiple-row
functions functions
Single-Row Functions
◦ Manipulate data items
◦ Accept arguments and return one value
◦ Act on each row returned
◦ Return one result per row
◦ Can modify the data type
◦ Can be nested
Calling a Function in SQL
function_name (column|expression, [arg1, arg2,...])
◦◦ function_name
function_name Is
Is the
the name
name of
of the
the function
function
◦◦ column
column Is
Is any
any named
named
database
database column
column
◦◦ expression
expression Is
Is any
any character
character
string
string or
or calculated
calculated
expression
expression
◦◦ arg1,
arg1, arg2
arg2 Is
Is any
any argument
argument to
to
be
be
Number Functions
Function name Description
Round (column/expression, Round to a specified
precision) precision
Note: There are lots of other Number functions but the one highlights in the course
is more useful
Using ROUND Function
SELECT Round(5.255,0) as [Round to 0],
Round(5.255,1) as [Round to 1],
(a) Round Function
Round(5.255,2) as [Round to 2]
FROM test;
Output:
Round to 0 Round to 1 Round to 2
5 5.3 5.26
Text Functions
Text
functions
Case conversion String manipulation
functions functions
UCase Mid
LCase Trim
StrConv Len
Instr
Left / Right
Case Conversion Functions
Functions name Description
UCase (column/expression) Puts all the letters in uppercase
or
StrConv(column/expression, 1)
LCase (column/expression) Puts all the letters in lower case
or
StrConv(column/expression, 2)
StrConv(column/expression, 3) Capitalizes the first letter of each
word and puts all the other letters
in lowercase.
Using UCase & LCase
SELECT Ucase('smith') AS UpperCase,
Lcase('SMITH') As LowerCase
FROM test;
Output:
UpperCase LowerCase
SMITH smith
Using StrConv
SELECT StrConv('my name',1) as [UpperCase],
StrConv('my name',2) as [LowerCase],
StrConv('my name',3) as [Initial]
FROM test;
Output:
UpperCase LowerCase Initial
MY NAME my name My Name
Example of Case conversion
SELECT Ucase (CourseDesp) as [Course Name]
FROM Course;
Output:
Course Name
DIPLOMA IN COMPUTER STUDIES
DIPLOMA IN GAMING AND ANIMATION TECHNIQUES
DIPLOMA IN COMPUTING
DIPLOMA IN INFO-COMM TECHNOLOGY
DIPLOMA IN INFORMATION TECHNOLOGY
DIPLOMA IN NETWORK AND CYBERSECURITY
String Manipulation Functions
Perform operations of string such as creating
a substring
Extract a portion of the string to create a new
character string output
These functions are useful when you want to
lookup certain data /values from a particular
string.
String Manipulation Functions
Functions Name Description
Mid (stringexpression, start, length) Returns part of a string.
Len(stringexpression) Returns the length of a
string
MID Function
Mid (stringexpression, start, length)
Stringexpression
◦ The field or the value that will be use for the
function to manipulate.
Start
◦ Tells Access where in the stringexpression to
start retrieving from
Length
◦ The number of characters to extract. If the length
is absent, then function will returns the rest of
the string from where you start.
Example on MID function
SELECT Mid(CourseDesp,12,8) As [Middle of String],
Mid(CourseDesp,12) As [Rest of String]
FROM course;
Output:
Middle of String Rest of String
Computer Computer Studies
Gaming a Gaming and Animation Techniques
Computin Computing
Info-Com Info-Comm Technology
Informat Information Technology
Network Network and CyberSecurity
LEN Function
Len (stringexpression)
Stringexpression
◦ The field or the value that the length to be
calculate.
Example on LEN Function
SELECT MentorName, Len(MentorName) As [Length]
FROM Mentor;
Output:
Length
MentorName
Goile 5
Rimes 5
Christopher 11
Schubert 8
Norman 6
Carroll 7
Current Date and Time
Default format for Date in Access is
MM/D/YYYY
Functions to display system date and time:
Function Name Description
Date() Provides current date
Time() Provides current time
Now() Provides current date
and time
Display Current Date / Time
SELECT Date() As [Today's Date],Time() As [Current Time] ,
Now() As [Current Date and Time]
From Test;
Output:
Today's Date Current Time Current Date and Time
6/1/2005 9:15:40 PM 6/1/2005 9:15:40 PM
Arithmetic with Dates
◦◦ Add
Add or
or subtract
subtract aa number
number toto or
or from
from aa date
date to
to obtain
obtain
aa date
date value
value
◦◦ Subtract
Subtract two
two dates
dates to
to find the number
find the number of of days
days
between
between those
those dates
dates
Using Arithmetic operator with
Dates
SELECT LastName, DateEnrolled , DateEnrolled+30 As [New Date]
from Student
Where LastName = 'Lee';
Output:
LastName DateEnrolled New Date
Lee 05-Jan-02 2/4/2002
Using Arithmetic operator with
Dates(2)
SELECT LastName, (Date() -DateEnrolled)/7 As [Week in School]
from Student
Where CourseID = 'DCS';
Output:
LastName Week in School
Bartell 204.714285714286
Mikulski 122.714285714286
Tham 121.714285714286
Williams 161.857142857143
Dates Functions
Function Name Description
Day(date) Extracts the day of the month from
a date.
Month(date) Extracts the month from a date.
Year(date) Extracts the year from a date.
Weekday(date) Extracts the day of the week from a
date.
DateDiff(interval, date1, Subtract number of day/month/year
date2) to a between two dates.
Using Dates functions – Year()
SELECT Year(DateEnrolled) AS [Year Enrolled] , Year(Date())-
Year(DateEnrolled) AS [No of years in schools]
FROM Student
where CourseID = 'DIT';
Output:
Year Enrolled No of years in schools
2002 4
2001 5
2001 5
2000 6
Using Dates functions – Month()
SELECT StudID, Month(DateEnrolled) AS [Month Enrolled] ,
Month(DateEnrolled)+6 AS [Sixth Month]
FROM Student
where CourseID = 'DIT';
Output:
StudID Month Enrolled Sixth Month
S003 1 7
S005 4 10
S011 4 10
S013 3 9
Using Dates functions – Day()
SELECT StudID, DateEnrolled, Day(DateEnrolled) AS [Date]
FROM Student
where CourseID = 'DIT';
Output:
StudID DateEnrolled Date
S003 05-Jan-02 5
S005 01-Apr-01 1
S011 01-Apr-01 1
S013 30-Mar-00 30
Interval value
The interval is the argument to instructs the function what
kind of setting you want to add/subtract to the date.
Setting Description
d Day
m Month
yyyy Year
w weekday
ww week
h Hour
n Minute
s second
Using DateAdd Functions
SELECT StudID,DateEnrolled, DateAdd('m',6,DateEnrolled) As
[Add 6 months]
FROM Student
WHERE CourseID = 'DIT';
Output:
StudID DateEnrolled Add 6 months
S003 05-Jan-02 7/5/2002
S005 01-Apr-01 10/1/2001
S011 01-Apr-01 10/1/2001
S013 30-Mar-00 9/30/2000
DateDiff Functions
Allow to subtract day/month/year between
two dates.
Depends on the interval setting.
Syntax:
◦ DateDiff(interval, date1, date2)
◦ If date1 > date 2, result will be in negative value.
Using DateDiff Functions
SELECT StudID,DateEnrolled, DateDiff('yyyy',DateEnrolled,Date())
As [Year]
FROM Student
WHERE CourseID = 'DIT';
Output:
StudID DateEnrolled Year
S003 05-Jan-02 4
S005 01-Apr-01 5
S011 01-Apr-01 5
S013 30-Mar-00 6
Using DateDiff Functions
SELECT DateDiff ('m',#01-JAN-2006#,#31-MAR-2006#) As
[Month Difference]
FROM TEST;
Output:
Month Difference
2
Formatting in Access
You can use the Format function to customize
the way numbers, dates, times, and text are
displayed and printed.
You can use one of the predefined formats or
you can create a custom format by using
formatting symbols.
The Format function uses different settings
for different data types.
Access Date Formats
Symbol Description
yyyy Full year (0100 to 9999).
yy Last two digits of the year (01 to 99).
mmmm Full name of the month (January to December).
mmm First three letters of the month (Jan to Dec).
mm Month of the year in two numeric digits (01 to 12).
dd Day of the month in two numeric digits (01 to 31).
ddd First three letters of the weekday (Sun to Sat).
dddd Full name of the weekday (Sunday to Saturday).
Example of Date Format
mm-dd-yyyy hh:nn:ss am/pm 12-31-2005 07:36:45 PM
mm-dd-yyyy hh:nn am/pm 12-31-2005 07:36 PM
dddd, mmmm dd, yyyy Saturday, December 31, 2005
dd-mmm-yy 31-Dec-05
mm-dd-yyyy 12-31-2005
hh:nn:ss am/pm 07:36:45 PM
hh:nn am/pm 07:36 PM
hh:nn 19:36
Displaying Formatted Date
SELECT StudID, LastName, Format(DateEnrolled,'dddd, mmm dd yyyy')
FROM Student;
Output:
StudID LastName Expr1002
S001 Bartell Friday, Feb 15 2002
S003 Lee Saturday, Jan 05 2002
S006 Mikulski Friday, Sep 12 2003
S007 Tham Friday, Sep 19 2003
S009 Nicosia Friday, Feb 01 2002
S012 Maser Thursday, Sep 25 2003
S014 Williams Thursday, Dec 12 2002
S015 Chan Thursday, Dec 12 2002
Displaying Formatted Date
SELECT Format(Date(), ' dd mmmm " year of " yyyy " Time:"
hh:nn') AS [Today's Date]
FROM Test;
Output:
Today's Date
15 December year of 2005 Time: 12.00
Access Number Formats
Symbol Description
. (period) Decimal separator.
, (comma) Thousand separator.
0 Digit placeholder. Display a digit or 0.
# Digit placeholder. Display a digit or nothing.
$ Display the literal character "$".
% Percentage. The value is multiplied by 100
and a percent sign is appended.
END
ANY QUESTION?
