In the last tutorial we left off with the SUM(column) function. In this article we will cover the remaining Aggregate Functions and the Scalar Functions. We might even make some fat jokes, some mama jokes, and as always, some nerd jokes.
With the Round function you can round off a number to a specific number of decimal places. Here is an example:
Select Round(1.75,1) as SampleColumn from Employees;
This would result in the number 2, as we are rounding up 1 decimal. Had the numbers in the parentheses been (1.76,2), the function would have returned 1.8.
Now()
Now returns the current date and time based on your computer's system date and time. If these are not properly set, the result will be wrong.
Select Now() As SampleColumn from Employees;
This would return something like: 10/20/2007 8:32:19 PM. "A long, long time ago" is not an option.
DateDiff(d,date1,date2)
This function returns the difference between two dates. The syntax is like this:
Select DateDiff('d',#10/10/2007#,Now()) as SampleColumn from Employees;
This would return the difference between 10/10/2007 and today's date. There are a lot of options you can use with the DateDiff function, which I do not have the space to go over. However, I will say that you can use an alternate Syntax, which is:
You can fool around with the Interval, firstdayofweek, and firstweekofyear sections by inserting data from the following tables:
For the Interval Section
What it does
YYYY
Year
Q
Quarter
M
Month
Y
Day of the Year
W
Weekday
WW
Week
H
Hour
N
Minute
S
Second
For Firstdayofweek
Value
What it Does
vbUseSystem
0
Uses the NLS API
VbSunday
1
Sunday
VbMonday
2
Monday
VbTuesday
3
Tuesday
etc
For Firstweekofyear
Value
What it Does
VbUseSystem
0
Uses the NLS API
VbFirstJan1
1
First Week in January
VbFirstFourDays
2
Starts with the first week of the year that has a minimum of four days
VbFirstFullWeek
3
Starts with the first full week of the year
Well that's it for this episode. I did leave out two of the more complex functions listed, but don't worry: I will be doing a future tutorial on even more functions in SQL, and I will cover those there. Hope you enjoyed.
