All About SQL Functions - Round(c,decimals) (Page 6 of 6 )
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:
DateDiff ( interval, date1, date2[, firstdayofweek[, firstweekofyear]] )
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.
Till then...
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
