Microsoft query built in functions

InStrRev Function. Int, Fix Functions. IPmt Function. IRR Function. IsArray Function. IsDate Function. IsEmpty Function. IsError Function. IsMissing Function. IsNull Function. IsNumeric Function. IsObject Function. Join Function.

LBound Function. LCase Function. Left Function. Len Function. Loc Function. LOF Function. Log Function. MacID Function. MacScript Function. Mid Function. Min, Max Functions. Minute Function.

MIRR Function. Month Function. MonthName Function. MsgBox Function. Now Function. NPer Function. NPV Function. Nz Function. Oct Function. Partition Function. Pmt Function. PPmt Function. PV Function. QBColor Function. Rate Function. Replace Function. RGB Function. Right Function. Rnd Function. Round Function. Second Function. Seek Function. Sgn Function.

Shell Function. Sin Function. SLN Function. Space Function. Spc Function. Split Function. Sqr Function. Str Function. Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.

Operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid. SQL Server built-in functions are either deterministic or nondeterministic. Functions are deterministic when they always return the same result any time they are called by using a specific set of input values.

Functions are nondeterministic when they could return different results every time they are called, even with the same specific set of input values. Functions that take a character string input and return a character string output use the collation of the input string for the output. Functions that take non-character inputs and return a character string use the default collation of the current database for the output.

Functions that take multiple character string inputs and return a character string use the rules of collation precedence to set the collation of the output string. Skip to main content. This browser is no longer supported. Download Microsoft Edge More info.

Contents Exit focus mode. What are the SQL database functions? In other words, it calculates the difference between two dates, and you get to pick the interval by which the function calculates that difference. Let us now say we want to calculate our authors' age. For this, we first we need to create a new query and add our authors table and then add FirstName, LastName, and the BirthDay fields. We can calculate people's age by calculating the difference between their date of birth, or birthday and whatever today's date is.

The Format Function returns a string, containing an expression formatted according to instructions contained in a format expression. Here is the list of user-defined formats which can be used in Format function. Type the Format Function. The first function argument will be an expression, which can be almost anything. Let us now have the birthday field as the first and the next thing is to write our format.

In this case, we need month, month, day, day. In the next field, it is returning the first 3 character from the name of the month for that birthday and in the last field you will get the full month name. It has up to three function arguments, all of which are required. Let us take a simple example. We will create a new query using query design and add tblAuthors table and then add the following fields. You can now see we have three fields — FirstName, MiddleInitial, LastName, and then this concatenated field, which is pulling all three fields together.

Let us run your query to see the result of this query. Now, you can see the result of the query, but you will also notice that some records do not have a middle initial. For example, the Joyce Dyer record does not have a middle initial, but in the FullName field you will see the period that really doesn't need to be there. So, go back to the Design View. Here, we will concatenate the name in a different way using the IIf Function.