Knowledgebase: Syntax
[QODBC-Desktop] QODBC Function List
Posted by Juliet (QODBC Support) on 28 July 2009 09:29 AM

QODBC Functions

QODBC Functions

     This is a list of all of the SQL functions supported by the QODBC Driver and their associated syntax.

QODBC String Functions

ASCII ( string_exp )

Instruction: Returns the ASCII code value of the leftmost character of string_exp as integer.

Example: SELECT {fn ASCII("Name")} AS "ASCII", "Name" FROM Customer

Returns:

ASCII Name
65 Adam's Candy Shop
65 Andres, Cristina
66 Balak, Mike
51 330 Main St
82 Residential
66 Blackwell, Edward
67 Chapman, Natalie
67 Cheknis, Benjamin
67 Corcoran, Carol

 

CHAR ( code )

Instruction: Returns the character that has the ASCII code value specified by code. The value of code should be between 0 and 255; otherwise, the return value is data source-dependent.

Example: SELECT {fn CHAR(65)} +  {fn CHAR(66)} AS "APlusB", "Name" FROM Customer

Returns:

APlusB Name
AB Adam's Candy Shop
AB Andres, Cristina
AB Balak, Mike
AB 330 Main St
AB Residential

 

CONCAT ( string_exp1, string_exp2 )

Instruction: Returns a character string that is the result of concatenating string_exp2 to string_exp1. If the column represented by string_exp1 or string_exp2 contains a NULL value, a NULL value will be returned.

Example: SELECT {fn CONCAT("BillAddressState", "BillAddressPostalCode")} AS "STZip", "Name" FROM Customer

Returns:

STZip Name
CA94555 Adam's Candy Shop
CA94326 Andres, Cristina
CA94326 Balak, Mike
CA94326 330 Main St
CA94326 Residential

 

DIFFERENCE ( string_exp1, string_exp2 )

Instruction: Returns an integer value that indicates the difference between the values returned by the SOUNDEX function for string_exp1 and string_exp2.

Example: SELECT {fn DIFFERENCE("Name", 'Abercrombie, Kristy')} AS "Difference", "Name" FROM Customer

Returns:

Difference Name
199949 Adam's Candy Shop
1102999 Andres, Cristina
9910829 Balak, Mike
1099949 330 Main St
169901831 Residential

 

INSERT ( string_exp1, start, length, string_exp2 )

Instruction: Returns a character string where length characters have been deleted from the string_exp1 beginning at the start and where string_exp2 has been inserted into string_exp1, beginning at start.

Example: SELECT {fn INSERT("Name", 3, 2, '*Inserted*')} AS "Inserted", "Name" FROM Customer

Returns:

Inserted Name
Ad*Inserted*'s Candy Shop Adam's Candy Shop
An*Inserted*es, Cristina Andres, Cristina
Ba*Inserted*k, Mike Balak, Mike
33*Inserted* Main St 330 Main St
Re*Inserted*dential Residential

 

LCASE ( string_exp )

Instruction: Converts all uppercase characters in string_exp to lower case.

Example: SELECT {fn LCASE("Name")} AS "LCase", "Name" FROM Customer

Returns:

LCase Name
adam's candy shop Adam's Candy Shop
andres, cristina Andres, Cristina
balak, mike Balak, Mike
330 main st 330 Main St
residential Residential

 

UCASE ( string_exp )

Instruction: Converts all lower case characters in string_exp to upper case.

Example: SELECT {fn UCASE("Name")} AS "UCase", "Name" FROM Customer

Returns:

UCase Name
ADAM'S CANDY SHOP Adam's Candy
ANDRES, CRISTINA Andres, Cristina
BALAK, MIKE Balak, Mike
330 MAIN ST 330 Main St
RESIDENTIAL Residential

 

LEFT ( string_exp, count )

Instruction: Returns the leftmost count of characters of string_exp .

Example: SELECT {fn LEFT("Name", 5)} AS "Left5", "Name" FROM Customer

Returns:

Left5 Name
Adam' Adam's Candy Shop
Andre Andres, Cristina
Balak Balak, Mike
330 M 330 Main St
Resid Residential

 

RIGHT ( string_exp, count )

Instruction: Returns the rightmost count of characters of string_exp .

Example: SELECT {fn RIGHT(“Name”, 5)} AS "Right5", "Name" FROM Customer

Returns:

Right5 Name
Shop Adam's Candy Shop
Stina Andres, Cristina
Mike Balak, Mike
In St 330 Main St
ntial Residential

 

LENGTH ( string_exp )

Instruction: Returns the number of characters in string_exp , excluding trailing blanks and the string termination character.

Example: SELECT {fn LENGTH("Name")} AS "Length", "Name" FROM Customer

Returns:

Length Name
17 Adam's Candy Shop
16 Andres, Cristina
11 Balak, Mike
11 330 Main St
11 Residential

 

BIT_LENGTH ( string_exp )

Instruction: Returns the bit length of string_exp , excluding trailing blanks and the string termination character.

Example: SELECT {fn BIT_LENGTH("Name")} AS "BitLength", "Name" FROM Customer

Returns:

BitLength Name
136 Adam's Candy Shop
128 Andres, Cristina
88 Balak, Mike
88 330 Main St
88 Residential

 

CHAR_LENGTH ( string_exp )

Instruction: Returns the number of chars in string_exp , excluding trailing blanks and the string termination character.

Example: SELECT {fn CHAR_LENGTH("Name")} AS "CharLength", "Name" FROM Customer

Returns:

CharLength Name
17 Adam's Candy Shop
16 Andres, Cristina
11 Balak, Mike
11 330 Main St
11 Residential

 

CHARACTER_LENGTH ( string_exp )

Instruction: Returns the number of characters in string_exp , excluding trailing blanks and the string termination character. (Almost the same as LENGTH function)

Example: SELECT {fn CHARACTER_LENGTH("Name")} AS "CharacterLength", "Name" FROM Customer

Returns:

CharacterLength Name
17 Adam's Candy Shop
16 Andres, Cristina
11 Balak, Mike
11 330 Main St
11 Residential

 

OCTET_LENGTH ( string_exp )

Instruction: Returns the octet length of string_exp , excluding trailing blanks and the string termination character. (Almost the same as LENGTH function)

Example: SELECT {fn OCTET_LENGTH("Name")} AS "OctetLength", "Name" FROM Customer

Returns:

OctetLength Name
17 Adam's Candy Shop
16 Andres, Cristina
11 Balak, Mike
11 330 Main St
11 Residential

 

LOCATE ( string_exp1, string_exp2[, start] )

Instruction: Returns the starting position of the first occurrence of string_exp1 within string_exp2 . The search for the first occurrence of string_exp1 begins with the first position in string_exp2 unless the optional argument, start is specified. If start is specified, the search begins with the character position indicated by the value of start . The first character position in string_exp2 is indicated by the value 1. If string_exp1 is not found within string_exp2 , the value 0 is returned.

Example: SELECT {fn LOCATE('a', "Name", 2)} AS "LocationOfA", "Name" FROM Customer

Returns:

LocationOfA Name
3 Adam's Candy Shop
16 Andres, Cristina
2 Balak, Mike
6 330 Main St
10 Residential

 

LTRIM ( string_exp )

Instruction: Returns the characters of string_exp , with leading blanks removed.

Example: SELECT {fn LTRIM("Name")} AS "LTrim", "Name" FROM Customer

Returns:

LTrim Name
Adam's Candy Adam's Candy Shop
Andres, Cristina Andres, Cristina
Balak, Mike Balak, Mike
330 Main St 330 Main St
Residential Residential

 

RTRIM ( string_exp )

Instruction: Returns the characters of string_exp , with trailinging blanks removed.

Example: SELECT {fn RTRIM("Name")} AS "RTrim", "Name" FROM Customer

Returns:

RTrim Name
Adam's Candy Shop Adam's Candy Shop
Andres, Cristina Andres, Cristina
Balak, Mike Balak, Mike
330 Main St 330 Main St
Residential Residential

 

REPEAT ( string_exp1, repeattimes )

Instruction: Returns the characters of string_exp1, with repeating it repeattimes.

Example: SELECT {fn REPEAT("Name",2)} as "Repeat2","Name" FROM Customer

Returns:

Repeat2 Name
Adam's Candy Shop Adam's Candy Shop Adam's Candy Shop
Andres, Cristina Andres, Cristina Andres, Cristina
Balak, Mike Balak, Mike Balak, Mike
330 Main St330 Main St 330 Main St
Residential Residential Residential

 

POSITION ( string_exp1, string_exp2 )

Instruction: Returns a integer value that shows the position where string_exp1 first begins in string_exp2 (including spaces)

Example: SELECT {fn POSITION('a' IN "Name")} As "PositionOfA", "Name" FROM Customer

Returns:

PositionOfA Name
1 Adam's Candy Shop
1 Andres, Cristina
2 Balak, Mike
0 330 Main St
0 Residential

 

REPLACE ( string_exp1, string_exp2,string_exp3 )

Instruction: Returns the characters of string_exp3 which takes the place of string_exp2 value in column string_exp1 .

Example: SELECT {fn REPLACE("NAME",'330 Main St','abc')} AS "Replace","Name" FROM Customer

Returns:

Replace Name
Adam's Candy Shop Adam's Candy Shop
Andres, Cristina Andres, Cristina
Balak, Mike Balak, Mike
abc 330 Main St
Residential Residential

 

SOUNDEX ( string_exp )

Instruction: Returns a character string representing the sound of the words in string_exp .

Example: SELECT {fn SOUNDEX("Name")} AS "Soundex", "Name" FROM Customer

Returns:

Soundex Name
ADAMACACAMDACAB Adam's Candy Shop
AMDRACACRACDAMA Andres, Cristina
BALACAMACA Balak, Mike
AMAMACD 330 Main St
RACADAMDAL Residential

 

SPACE ( count )

Instruction: Returns a character string consisting of count spaces.

Example: SELECT '[' + {fn SPACE(10)} + ']' AS "TenSpaces", "Name" FROM Customer

Returns:

TenSpaces Name
[ ] Adam's Candy Shop
[ ] Andres, Cristina
[ ] Balak, Mike
[ ] 330 Main St
[ ] Residential

 

SUBSTRING ( string_exp, start, length )

Instruction: Returns a character string that is derived from string_exp beginning at the character position specified by start for length characters.

Example: SELECT {fn SUBSTRING("Name", 2, 5)} AS "Middle5Characters", "Name" FROM Customer

Returns:

Middle5Characters Name
dam's Adam's Candy Shop
ndres Andres, Cristina
alak, Balak, Mike
30 Ma 330 Main St
eside Residential

 

 

QODBC Numeric Functions

ABS ( numeric_exp|float_exp|integer_exp )

Instruction: Returns the absolute value of numeric_exp .

Example: SELECT "Name", {fn ABS(Balance)} AS "ABSBalance", "Balance" FROM Customer

Returns:

Name ABSBalance Balance
Adam's Candy 40.00 40.00
Andres, Cristina 0.00 0.00
Balak, Mike 0.00 0.00
330 Main St 180.00 180.00
Residential 0.00 0.00

 

ACOS ( float_exp )

Instruction: Returns the arccosine of float_exp as an angle, expressed in radians.

Example: SELECT {fn ACOS({fn CONVERT(0, SQL_FLOAT)})} AS "ACOSValue", CompanyName AS "CompanyName" FROM Company

Returns:

ACOSValue CompanyName
1.570796 Larry's

 

ASIN ( float_exp )

Instruction: Returns the arcsine of float_exp as an angle, expressed in radians.

Example: SELECT {fn ASIN({fn CONVERT(0, SQL_FLOAT)})} AS "ASINValue", CompanyName AS "CompanyName" FROM Company

Returns:

ASINValue CompanyName
1.570796 Larry's

 

ATAN ( float_exp )

Instruction: Returns the arctangent of float_exp as an angle, expressed in radians.

Example: SELECT {fn ATAN({fn CONVERT(1, SQL_FLOAT)})} AS "ATANValue", CompanyName AS “CompanyName” FROM Company

Returns:

ATANValue CompanyName
0.785398 Larry's

 

ATAN2 ( float_exp1, float_exp2 )

Instruction: Returns the arctangent of the x and y coordinates specified by float_exp1 and float_exp2 , respectively, as an angle, expressed in radians.

Example: SELECT {fn ATAN2({fn CONVERT(1, SQL_FLOAT)}, {fn CONVERT(2, SQL_FLOAT)})} AS "ATAN2Value", CompanyName AS “CompanyName” FROM Company

Returns:

ATAN2Value CompanyName
0.463648 Larry's

 

COS ( float_exp )

Instruction: Returns the cosine of float_exp, where float_exp is an angle  expressed in radians.

Example: SELECT {fn COS({fn CONVERT(1, SQL_FLOAT)})} AS "COSValue", CompanyName AS “CompanyName” FROM Company

Returns:

COSValue CompanyName
0.540302 Larry's

 

COT ( float_exp )

Instruction: Returns the cotangent of float_exp, where float_exp is an angle  expressed in radians.

Example: SELECT {fn COT({fn CONVERT(1, SQL_FLOAT)})} AS "COTValue", CompanyName AS “CompanyName” FROM Company

Returns:

COTValue CompanyName
0.642093 Larry's

 

CEILING ( numeric_exp|float_exp|integer_exp )

Instruction: Returns the smallest integer greater than or equal to numeric_exp .

Example: SELECT "Name", {fn CEILING("Balance")} AS "CeilingBalance", "Balance" FROM Customer

Returns:

Name CeilingBalance Balance
Adam's Candy 40.00 40.00
Andres, Cristina 0.00 0.00
Balak, Mike 0.00 0.00
330 Main St 180.00 180.00
Residential 0.00 0.00

 

DEGREES ( numeric_exp|float_exp|integer_exp )

Instruction: Returns the number of degrees converted from numeric_exp radians.

Example: SELECT {fn DEGREES(1)} AS "DegreesReturned", CompanyName AS “CompanyName” FROM Company

Returns:

DegreesReturned CompanyName
57.29578 Larry's

 

EXP ( float_exp )

Instruction: Returns the exponential value of float_exp .

Example: SELECT {fn EXP({fn CONVERT(1, SQL_FLOAT)})} AS "ExpReturned", CompanyName AS “CompanyName” FROM Company

Returns:

ExpReturned ExpReturned
2.718282 Larry's

 

FLOOR (numeric_exp|float_exp|integer_exp)

Instruction: Returns largest integer less than or equal to numeric_exp .

Example: SELECT "Name", {fn FLOOR("Balance")} AS "FloorBalance", "Balance" FROM Customer

Returns:

Name FloorBalance Balance
Adam's Candy 40.00 40.00
Andres, Cristina 0.00 0.00
Balak, Mike 0.00 0.00
330 Main St 180.00 180.00
1456 Red Cloud 25671.00 25671.35

 

LOG ( float_exp )

Instruction: Returns the natural logarithm of float_exp .

Example: SELECT {fn LOG({fn CONVERT(25, SQL_FLOAT)})} AS "LogReturned", CompanyName AS “CompanyName” FROM Company

Returns:

LogReturned CompanyName
3.218876 Larry's

 

LOG10 ( float_exp )

Instruction: Returns the base 10 logarithm of float_exp .

Example: SELECT {fn LOG10({fn CONVERT(25, SQL_FLOAT)})} AS "Log10Returned", CompanyName AS “CompanyName” FROM Company

Returns:

Log10Returned CompanyName
1.39794 Larry's

 

MOD ( integer_exp1, integer_exp2 )

Instruction: Returns the remainder (modulus) of integer_exp1 divided by integer_exp2 .

Example: SELECT {fn MOD(27, 7)} AS "Mod7Returned", CompanyName AS “CompanyName” FROM Company

Returns:

Mod7Returned CompanyName
6 Larry's

 

PI ()

Instruction: Returns the constant value of pi as a floating point value.

Example: SELECT {fn PI()} AS "PI", CompanyName AS “CompanyName” FROM COMPANY

Returns:

PI CompanyName
3.141593 Larry's

 

POWER ( numeric_exp|float_exp|integer_exp, integer_exp )

Instruction: Returns the value of numeric_exp to the power of integer_exp .

Example: SELECT {fn POWER(4, 3)} AS "PowerValue", CompanyName AS "CompanyName" FROM COMPANY

Returns:

PowerValue CompanyName
64 Larry's

 

RADIANS ( numeric_exp|float_exp|integer_exp )

Instruction: Returns the number of radians converted from numeric_exp degrees.

Example: SELECT {fn RADIANS(57.29578)} AS "RadiansValue", CompanyName AS “CompanyName” FROM COMPANY

Returns:

RadiansValue CompanyName
1 Larry's

 

RAND ([ integer_exp|float_exp|numeric_exp ])

Instruction: Returns a random floating point value using integer_exp as optional seed value.

Example: SELECT {fn RAND()} AS "RandValue" FROM COMPANY

Returns:

RandValue
4035516600699198138

 

ROUND ( numeric_exp|float_exp|integer_exp, integer_exp )

Instruction: Returns numeric_exp rounded to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is rounded to | integer_exp | places to the left of the decimal point.

Example: SELECT "Name", {fn ROUND(Balance, 1)} AS "RoundBalance", "Balance" FROM Customer

Returns:

Name RoundBalance Balance
Adam's Candy Shop 40.00 40.00
Andres, Cristina .00 0.00
Balak, Mike .00 0.00
330 Main St 180.00 180.00
Residential .00 0.00

 

SIGN ( numeric_exp|float_exp|integer_exp )

Instruction: Returns an indicator or the sign of numeric_exp . If numeric_exp is less than zero, -1 is returned. If numeric_exp equals zero, 0 is returned. If numeric_exp is greater than zero, 1 is returned.

Example: SELECT "Name", {fn SIGN(Balance)} AS "SignOfBalance", "Balance" FROM Customer

Returns:

Name SignOfBalance Balance
Adam's Candy 1 40.00
Andres, Cristina 1 0.00
Balak, Mike 1 0.00
330 Main St 1 180.00
Residential 1 0.00

 

SIN ( float_exp )

Instruction: Returns the sine of float_exp, where float_exp is an angle  expressed in radians.

Example: SELECT {fn SIN({fn CONVERT(1, SQL_FLOAT)})} AS "SINValue", CompanyName AS “CompanyName” FROM Company

Returns:

SINValue CompanyName
0.841471 Larry's

 

SQRT ( float_exp )

Instruction: Returns the square root of float_exp .

Example: SELECT {fn SQRT({fn CONVERT(47, SQL_FLOAT)})} AS "SQRTValue", CompanyName AS “CompanyName” FROM Company

Returns:

SQRTValue CompanyName
6.855655 Larry's

 

TAN ( float_exp )

Instruction: Returns the tangent of float_exp, where float_exp is an angle  expressed in radians.

Example: SELECT {fn TAN({fn CONVERT(1, SQL_FLOAT)})} AS "TANValue", CompanyName AS “CompanyName” FROM Company

Returns:

TANValue CompanyName
1.557408 Larry's

 

TRUNCATE ( numeric_exp|float_exp|numeric_exp, integer_exp )

Instruction: Returns numeric_exp truncated to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is truncated to | integer_exp | places to the left of the decimal point.

Example: SELECT "Name", {fn TRUNCATE(Balance, 1)} AS "TruncateBalance", "Balance" FROM Customer

Returns:

Name TruncateBalance Balance
Adam's Candy 40.00 40.00
Andres, Cristina .00 0.00
Balak, Mike .00 0.00
330 Main St 180.00 180.00
Residential .00 0.00

 

 

QODBC Time and Date Functions

CURDATE ()

Instruction: Returns the current date as a date value.

Example: SELECT {fn CURDATE()} AS "CurDate" FROM Company

Returns:

CurDate
2009-07-08

 

CURRENT_DATE ()

Instruction: Returns the current date as a date value.

Example: SELECT {fn CURDATE_DATE()} AS "CurrentDate" FROM Company

Returns:

CurrentDate
2009-07-08

 

CURTIME()

Instruction: Returns the local time as a time value.

Example: SELECT {fn CURTIME()} AS "CurTime" FROM Company

Returns:

CurTime
20:30:31

 

CURRENT_ TIME()

Instruction: Returns the current time as a time value.

Example : SELECT {fn CURRENT_TIME()} AS "CurrentTime" FROM Company

Returns:

CurrentTime
20:31:03

 

CURRENT_ TIMESTAMP()

Instruction: Returns the current date and time as a timestamp value.

Example: SELECT {fn CURRENT_TIMESTAMP()} AS "CurrentTimeStamp" FROM Company

Returns:

CurrentTimeStamp
2009-07-08 23:10:10.316

 

DAYNAME ( date_exp )

Instruction: Returns a character string containing the data source-specific name of the day (for example, Sunday, through Saturday or Sun. through Sat. for a data source that uses English) for the day portion of date_exp .

Example: SELECT {fn DAYNAME({fn CURDATE()})} AS "CurDayName", CompanyName AS "CompanyName" FROM Company

Returns:

CurDayName CompanyName
Thursday Larry's

 

DAYOFMONTH ( date_exp| string_exp|timestamp_exp )

Instruction: Returns the day of the month in date_exp as an integer value in the range of 1-31.

Example: SELECT {fn DAYOFMONTH({fn CURDATE()})} AS "CurDayOfMonth" FROM Company

Returns:

CurDayOfMonth
08

 

DAYOFWEEK ( date_exp )

Instruction: Returns the day to the week in date_exp as an integer value in the range of 1-7, where 1 represents Sunday.

Example: SELECT {fn DAYOFWEEK({fn CURDATE()})} AS "CurDayOfWeek" FROM Company

Returns:

CurDayOfWeek
3

 

DAYOFYEAR ( date_exp )

Instruction: Returns the day of the year in date_exp as an integer value in the range of 1-366.

Example: SELECT {fn DAYOFYEAR({fn CURDATE()})} AS "CurDayOfYear" FROM Company

Returns:

CurDayOfYear
189

 

EXTRACT( date_exp|timestamp_exp )

Instruction: Returns the part of date_exp or timestamp_exp .

Example: SELECT {fn EXTRACT(YEAR FROM{fn CURRENT_DATE()})} AS "Year",{fn EXTRACT(MONTH FROM{fn CURRENT_DATE()})} AS "Month",{fn EXTRACT(DAY FROM{fn CURRENT_DATE()})} AS "Day",CompanyName as "CompanyName" FROM Company

Returns:

Year Month Day CompanyName
2009 7 9 Larry's

 

HOUR ( time_exp| string_exp|timestamp_exp )

Instruction: Returns the hour in time_exp or char_exp or timestamp_exp as an integer value in the range of 0-23.

Example: SELECT {fn HOUR({fn CURTIME()})} AS "CurHour" FROM Company

Returns:

CurHour
22

 

MINUTE ( time_exp| string_exp|timestamp_exp )

Instruction: Returns the minute in time_exp as an integer value in the range of 0-59.

Example: SELECT {fn MINUTE({fn CURTIME()})} AS "CurMinute" FROM Company

Returns:

CurMinute
59

 

MONTH ( date_exp| string_exp|timestamp_exp )

Instruction: Returns the month in date_exp as an integer value in the range of 1-12.

Example: SELECT {fn MONTH({fn CURDATE()})} AS "CurMonth" FROM Company

Returns:

CurMonth
07

 

MONTHNAME ( date_exp| string_exp|timestamp_exp )

Instruction: Returns a character string containing the data source-specific name of the month (for example, January through December or Jan. through Dec. for a data source that uses English) for the month portion of date_exp .

Example: SELECT {fn MONTHNAME({fn CURDATE()})} AS "CurMonthName” , CompanyName AS "CompanyName" FROM Company

Returns:

CurMonthName CompanyName
July Larry's

 

NOW ()

Instruction: Returns the current date and time as a timestamp value.

Example: SELECT {fn NOW()} AS "Now" FROM Company

Returns:

Now
2009-07-08 23:05:45.144

 

QUARTER ( date_exp| string_exp|timestamp_exp )

Instruction: Returns the quarter in the date_exp as an integer value in the range of 1-4, where 1 represents January 1 through March 31.

Example: SELECT {fn QUARTER({fn CURDATE()})} AS "CurQuarter", CompanyName AS "CompanyName" FROM Company

Returns:

CurQuarter CompanyName
3 Larry's

 

SECOND ( time_exp| string_exp|timestamp_exp )

Instruction: Returns the second in time_exp as an integer value in the range of 0-59.

Example: SELECT {fn SECOND({fn CURTIME()})} AS "CurSecond" FROM Company

Returns:

CurSecond
09

 

TIMESTAMPADD ( interval, integer_exp, timestamp_exp )

Instruction: Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp . Valid values of interval are the following keywords: SQL_TSI_FRAC_SECOND, SQL_TSI_HOUR, SQL_TSI_SECOND, SQL_TSI_DAY, SQL_TSI_MINUTE, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR where fractional seconds are expressed in billionths of a second.

Notes: If timestamp_exp is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of timestamp_exp is set to the current date before calculating the resulting timestamp.

     If timestamp_exp is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of timestamp_exp is set to 0 before calculating the resulting timestamp.

Example: SELECT Name, {fn TIMESTAMPADD(SQL_TSI_YEAR, 1, HiredDate)} AS "Anniversary" FROM Employee

Returns:

Name Anniversary
Duncan Fisher 2012-06-15 00:00:00.000
Jenny Miller 2012-11-01 00:00:00.000
Shane B. Hamby 2012-06-18 00:00:00.000

 

TIMESTAMPDIFF ( interval, timestamp_exp1, timestamp_exp2 )

Instruction: Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1 . Valid values of interval are the following keywords: SQL_TSI_FRAC_SECOND, SQL_TSI_HOUR, SQL_TSI_SECOND, SQL_TSI_DAY, SQL_TSI_MINUTE, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR where fractional seconds are expressed in billionths of a second.

Note: If either timestamp expression is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of that timestamp is set to the current date before calculating the difference between the timestamps.

     If either timestamp expression is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of that timestamp is set to 0 before calculating the difference between the timestamps.

Example: SELECT Name, {fn TIMESTAMPDIFF(SQL_TSI_YEAR, {fn CURDATE()}, HiredDate)} AS "YearsWorked” FROM Employee

Returns:

Name YearsWorked
Duncan Fisher 2
Jenny Miller 2
Shane B. Hamby 2

  

WEEK ( date_exp )

Instruction: Returns the week of the year in date_exp as an integer value in the range of 1-53.

Example: SELECT {fn WEEK({fn CURDATE()})} AS "CurWeek" FROM Company

Returns:

CurWeek
27

 

YEAR ( date_exp|string_exp|timestamp_exp )

Instruction: Returns the year in date_exp as an integer value.

Example: SELECT {fn YEAR({fn CURDATE()})} AS "CurYear" FROM Company

Returns:

CurYear
2009

 

 

QODBC System Functions

DATABASE ()

Instruction: Returns the name of the database in use at the time this function is called.

Example: SELECT {fn DATABASE()} AS "OpenDatabase" FROM Company

Returns:

OpenDatabase CompanyName
C:\Documents and Settings\All Users\Documents\Intuit\QuickBooks\Sample Company Files\QuickBooks Enterprise Solutions 9.0\sample_service-based business.qbw Larry's

  

IFNULL ( exp, value )

Instruction: If exp is null, value is returned. If exp is not null, exp is returned. The possible data type(s) of value must be compatible with the data type of exp .

Example: Select Name, {fn IFNULL(Fax, 'Missing Fax')} as "FixedFax" from Employee

Returns:

Name FixedFax
Duncan Missing Fax
Jenny Miller Missing Fax
Shane B. Hamby Missing Fax

  

PARENT( string_exp )

Instruction: Returns the characters before the colon it finds in string_exp

Example : SELECT {fn PARENT(FullName)} AS “ParentValue”,FullName FROM Account CALLDIRECT

Returns:

ParentValue FullName
Truck Truck:Accumulated Depreciation
QuickBooks Credit Card QuickBooks Credit Card
QuickBooks Credit Card QuickBooks Credit Card:QBCC Field Office

 

CHILD ( string_exp )

Instruction: Returns the characters after the colon it finds in string_exp

Example: SELECT {fn CHILD(FullName)} AS “ChildValue”,FullName FROM Account CALLDIRECT

Returns:

ChildValue FullName
Accumulated Depreciation Truck:Accumulated Depreciation
QuickBooks Credit Card QuickBooks Credit Card
QBCC Field Office QuickBooks Credit Card:QBCC Field Office

 

CONVERT (integer_exp, sqltype_exp)

Instruction: Returns the slqtype value which is converted from an integer value and can be used in numeric functions.

Example: SELECT {fn CONVERT(21,SQL_FLOAT)} AS "ConverValue", CompanyName AS "CompanyName" FROM Company

Returns:

ConverValue CompanyName
21 Larry's

 

 

(698 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).