Unit 5 Flashcards
function produces a date by adding a specified number to a specified part of a date.
DATEADD
function returns the difference between two parts of a date.
DATEDIFF
function subtracts a specified time interval from a date.
DATE_SUB()
function returns the specified part of the date requested.
DATEPART
function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc.
EXTRACT()
function will extract the year from a value stored as a SMALLDATETIME data type.
YEAR
function will extract the month from a date.
MONTH
function extracts the day of the month from a date
DAY
function returns the current system date and time
GETDATE
function returns the current system date and time in Universal Time Coordinate Format, useful for getting date and time of other location.
GETUTCDATE
Returns the current date and time
NOW()
Returns the current date
CURDATE()
Returns the current time
CURTIME()
Returns the current utc date
UTC_DATE()
Returns the current utc time
UTC_TIME()
operate on values in single rows, one row at a time.
Row-level Functions
function rounds numbers to a specified number of decimal places.
ROUND
(attribute), which returns the next larger integer value when a number contains decimal places.
CEILING
(attribute), which returns the next lower integer value when a number contains decimal places.
FLOOR
(attribute), which returns the square root of positive numeric values.
SQRT
(attribute), which returns the absolute value of any numeric value.
ABS
(attribute), which returns a number squared.
SQUARE
To handle the null issue, SQL Server provides a row-level function, which returns a value if a table value is null.
Isnull
A function, which returns a NULL if expression1 = expression2. If the expressions are not equal, then expression1 is returned.
NULLIF
function is used to display or return from a result set the rows that fall at the top of a range specified by an ORDER BY clause.
TOP
returns a certain percentage of rows that fall at the top of a specified range.
PERCENT
clause is used in the SELECT statement to constrain the number of rows in a result set. The —– clause accepts one or two arguments. The values of both arguments must be zero or positive integer constants.
LIMIT
specifies the offset of the first row to return. The offset of the first row is 0, not 1.
offset
specifies maximum number of rows to return.
count
is a specialized conversion function that always converts from a number (for example, float or numeric) to a character data type
STR
function is also used to explicitly convert to a given data type. But, the ——- function has additional limited formatting capabilities.
CONVERT
function returns part of a string
SUBSTRING
removes blanks from the beginning (left) of a string.
LTRIM
removes blanks from the end (right) of a string.
RTRIM
function returns the starting position of a specified pattern.
CHARINDEX
returns the substring from the given string before a specified number of occurrences of a delimiter.
SUBSTRING_INDEX()
function returns the starting position of a specified pattern
INSTR
To produce all the fields in the result set (output) in uppercase or in lowercase
UPPER/ LOWER
function returns the length (number of characters) of a desired string excluding trailing blanks.
LEN
