QODBC.com Tools for QuickBooks

Search

             building menu display...

QODBC Quick Links
What is QODBC
Download
Buy Now
How to Install QODBC
Connecting to QuickBooks
Using QuickBooks Remotely
64 Bit Connections
QODBC Version Differences
QODBC Technical Reference
Tutorials
Support/FAQ
Reseller Program
OEM Program
        Go Back

QODBC Functions Reference

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) - 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

Abercrombie, Kristy

66

Baker, Chris

66

Balak, Mike

66

Barley, Renee

66

Bolinski, Rafal

50

2nd story addition

66

Bristol, Sonya

66

Burch, Jason

66

Burney, Tony

50

2nd story addition

50

2nd story addition

49

155 Wilks Blvd.

55

75 Sunset Rd.

 

CHAR(code) - 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

Abercrombie, Kristy

AB

2nd story addition

AB

2nd story addition

AB

2nd story addition

AB

155 Wilks Blvd.

AB

75 Sunset Rd.

 

CONCAT(string_exp1, string_exp2) - 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

CA94326

Abercrombie, Kristy

CA94327

2nd story addition

CA94482

2nd story addition

CA94326

2nd story addition

CA94482

155 Wilks Blvd.

CA94482

75 Sunset Rd.

                  

DIFFERENCE(string_exp1, string_exp2) - 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

0

Abercrombie, Kristy

1102829

2nd story addition

1102829

2nd story addition

1102829

2nd story addition

1001829

55 Wilks Blvd.

99949

75 Sunset Rd.

 

INSERT(string_exp1, start, length, string_exp2) - Returns a character string where length characters have been deleted from string_exp1 beginning at 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

Ab*Inserted*crombie, Kristy

Abercrombie, Kristy

2n*Inserted*story addition

2nd story addition

2n*Inserted*story addition

2nd story addition

2n*Inserted*story addition

2nd story addition

15*Inserted*Wilks Blvd.

155 Wilks Blvd.

75*Inserted*unset Rd.

75 Sunset Rd.

 

LCASE(string_exp) - Converts all upper case characters in string_exp to lower case.

Example:

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

Returns:

LCase

Name

abercrombie, kristy

Abercrombie, Kristy

2nd story addition

2nd story addition

2nd story addition

2nd story addition

2nd story addition

2nd story addition

155 wilks blvd.

155 Wilks Blvd.

75 sunset rd.

75 Sunset Rd.

 

LEFT(string_exp, count) - Returns the leftmost count of characters of string_exp.

Example:

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

Returns:

Left5

Name

Aberc

Abercrombie, Kristy

2nd s

2nd story addition

2nd s

2nd story addition

2nd s

2nd story addition

155 W

155 Wilks Blvd.

75 Su

75 Sunset Rd.

 

LENGTH(string_exp) - 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

19

Abercrombie, Kristy

18

2nd story addition

18

2nd story addition

18

2nd story addition

15

155 Wilks Blvd.

13

75 Sunset Rd.

 

LOCATE(string_exp1, string_exp2[, start]) - 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

0

Abercrombie, Kristy

11

2nd story addition

11

2nd story addition

11

2nd story addition

0

155 Wilks Blvd.

0

75 Sunset Rd.

 

LTRIM(string_exp) - Returns the characters of string_exp, with leading blanks removed.

Example:

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

Returns:

LTrim

Name

Abercrombie, Kristy

Abercrombie, Kristy

2nd story addition

2nd story addition

2nd story addition

2nd story addition

2nd story addition

2nd story addition

155 Wilks Blvd.

155 Wilks Blvd.

75 Sunset Rd.

75 Sunset Rd.

 

REPEAT(string_exp, count) -

Example:

SELECT {fn REPEAT('XO', 5)} AS "Repeat", "Name" FROM Customer

Returns:

Repeat

Name

XOXOXOXOXO

Abercrombie, Kristy

XOXOXOXOXO

2nd story addition

XOXOXOXOXO

2nd story addition

XOXOXOXOXO

2nd story addition

XOXOXOXOXO

155 Wilks Blvd.

XOXOXOXOXO

75 Sunset Rd.

 

 

RIGHT(string_exp, count) - Returns the rightmost count of characters of string_exp.

Example:

SELECT {fn RIGHT("Name", 5)} AS "Right5", "Name" FROM Customer

Returns:

Right5

Name

risty

Abercrombie, Kristy

ition

2nd story addition

ition

2nd story addition

ition

2nd story addition

Blvd.

155 Wilks Blvd.

t Rd.

75 Sunset Rd.

 

RTRIM(string_exp) - Returns the characters of string_exp, with trailinging blanks removed.

Example:

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

Returns:

RTrim

Name

Abercrombie, Kristy

Abercrombie, Kristy

2nd story addition

2nd story addition

2nd story addition

2nd story addition

2nd story addition

2nd story addition

155 Wilks Blvd.

155 Wilks Blvd.

75 Sunset Rd.

75 Sunset Rd.

 

SOUNDEX(string_exp) - 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

ABARCRAMBACRACDA

Abercrombie, Kristy

AMDACDARADADAM

2nd story addition

AMDACDARADADAM

2nd story addition

AMDACDARADADAM

2nd story addition

ALCABLBDA

155 Wilks Blvd.

ACAMCADARDA

75 Sunset Rd.

 

SPACE(count) - Returns a character string consisting of count spaces.

Example:

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

Returns:

TenSpaces

Name

[          ]

Abercrombie, Kristy

[          ]

2nd story addition

[          ]

2nd story addition

[          ]

2nd story addition

[          ]

155 Wilks Blvd.

[          ]

75 Sunset Rd.

 

SUBSTRING(string_exp, start, length) - 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

bercr

Abercrombie, Kristy

nd st

2nd story addition

nd st

2nd story addition

nd st

2nd story addition

55 Wi

155 Wilks Blvd.

5 Sun

75 Sunset Rd.

 

UCASE(string_exp) - Converts all lower case characters in string_exp to upper case.

Example:

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

Returns:

UCase

Name

ABERCROMBIE, KRISTY

Abercrombie, Kristy

2ND STORY ADDITION

2nd story addition

2ND STORY ADDITION

2nd story addition

2ND STORY ADDITION

2nd story addition

155 WILKS BLVD.

155 Wilks Blvd.

75 SUNSET RD.

75 Sunset Rd.

 

QODBC Numeric Functions

 

ABS(numeric_exp) - Returns the absolute value of numeric_exp.

Example:

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

Returns:

Name

ABSBalance

Balance

Utility Room

1099.95

-1099.95

Workshop

4735.73

4735.73

Wilks, Daniel

0.00

0.00

 

ACOS(float_exp) - Returns the arccosine of float_exp as an angle, expressed in radians.

Example:

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

Returns:

ACOSValue

1.570796

 

ASIN(float_exp) - Returns the arcsine of float_exp as an angle, expressed in radians.

Example:

SELECT {fn ASIN({fn CONVERT(1, SQL_FLOAT)})} AS "ASINValue" FROM Company

Returns:

ASINValue

1.570796

 

ATAN(float_exp) - Returns the arctangent of float_exp as an angle, expressed in radians.

Example:

SELECT {fn ATAN({fn CONVERT(1, SQL_FLOAT)})} AS "ATANValue" FROM Company

Returns:

ATANValue

0.785398

 

ATAN2(float_exp1, float_exp2) - 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" FROM Company

Returns:

ATAN2Value

0.463648

 

CEILING(numeric_exp) - 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

Utility Room

-1099.00

-1099.95

Workshop

4736.00

4735.73

Wilks, Daniel

0.00

0.00

 

COS(float_exp) - 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" FROM Company

Returns:

COSValue

0.540302

 

COT(float_exp) - 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" FROM Company

Returns:

COTValue

0.642093

 

DEGREES(numeric_exp) - Returns the number of degrees converted from numeric_exp radians.

Example:

SELECT {fn DEGREES(1)} AS "DegreesReturned" FROM Company

Returns:

DegreesReturned

57.29578

 

EXP(float_exp) - Returns the exponential value of float_exp.

Example:

SELECT {fn EXP({fn CONVERT(1, SQL_FLOAT)})} AS "ExpReturned" FROM Company

Returns:

ExpReturned

2.718282

 

FLOOR(numeric_exp) - 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

Utility Room

-1100.00

-1099.95

Workshop

4735.00

4735.73

Wilks, Daniel

0.00

0.00

 

LOG(float_exp) - Returns the natural logarithm of float_exp.

Example:

SELECT {fn LOG({fn CONVERT(25, SQL_FLOAT)})} AS "LogReturned" FROM Company

Returns:

LogReturned

3.218876

 

LOG10(float_exp) - Returns the base 10 logarithm of float_exp.

Example:

SELECT {fn LOG10({fn CONVERT(25, SQL_FLOAT)})} AS "Log10Returned" FROM Company

Returns:

Log10Returned

1.39794

 

MOD(integer_exp1, integer_exp2) - Returns the remainder (modulus) of integer_exp1 divided by integer_exp2.

Example:

SELECT {fn MOD(27, 7)} AS "Mod7Returned" FROM Company

Returns:

Mod7Returned

6

 

PI() - Returns the constant value of pi as a floating point value.

Example:

SELECT {fn PI()} AS "PI" FROM COMPANY

Returns:

PI

3.141593

 

POWER(numeric_exp, integer_exp) - Returns the value of numeric_exp to the power of integer_exp.

Example:

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

Returns:

PowerValue

64

 

RADIANS(numeric_exp) - Returns the number of radians converted from numeric_exp degrees.

Example:

SELECT {fn RADIANS(57.29578)} AS "RadiansValue" FROM COMPANY

Returns:

RadiansValue

1

 

RAND([integer_exp]) - Returns a random floating point value using integer_exp as optional seed value.

Example:

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

Returns:

RandValue

0.895865

 

ROUND(numeric_exp, integer_exp) - 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

Utility Room

-1099.90

-1099.95

Workshop

4735.70

4735.73

Wilks, Daniel

.00

0.00

 

SIGN(numeric_exp) - 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

Utility Room

-1

-1099.95

Workshop

1

4735.73

Wilks, Daniel

1

0.00

 

SIN(float_exp) - 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" FROM Company

Returns:

SINValue

0.841471

 

SQRT(float_exp) - Returns the square root of float_exp.

Example:

SELECT {fn SQRT({fn CONVERT(47, SQL_FLOAT)})} AS "SQRTValue" FROM Company

Returns:

SQRTValue

6.855655

 

TAN(float_exp) - 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" FROM Company

Returns:

TANValue

1.557408

 

TRUNCATE(numeric_exp, integer_exp) - 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

Utility Room

-1099.90

-1099.95

Workshop

4735.70

4735.73

Wilks, Daniel

.00

0.00

 

QODBC Time and Date Functions

 

CURDATE() - Returns the current date as a date value.

Example:

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

Returns:

CurDate

2004-10-01

 

CURTIME() - Returns the local time as a time value.

Example:

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

Returns:

CurTime

11:14:20

 

DAYNAME(date_exp) - 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" FROM Company

Returns:

CurDayName

Friday

 

DAYOFMONTH(date_exp) - 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

1

 

DAYOFWEEK(date_exp) - 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

6

 

DAYOFYEAR(date_exp) - 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

275

 

HOUR(time_exp) - Returns the hour in time_exp as an integer value in the range of 0-23.

Example:

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

Returns:

CurHour

15

 

MINUTE(time_exp) - 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

14

 

MONTH(date_exp) - 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

10

 

MONTHNAME(date_exp) - 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" FROM Company

Returns:

CurMonthName

October

 

NOW() - Returns the current date and time as a timestamp value.

Example:

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

Returns:

Now

2004-10-01 15:16:51.000

 

QUARTER(date_exp) - 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" FROM Company

Returns:

CurQuarter

4

 

SECOND(time_exp) - 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

33

 

TIMESTAMPADD(interval, integer_exp, timestamp_exp) - 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

Dan T. Miller

2007-11-01 00:00:00.000

Elizabeth N. Mason

2008-03-15 00:00:00.000

Gregg O. Schneider

2008-06-15 00:00:00.000

 

TIMESTAMPDIFF(interval, timestamp_exp1, timestamp_exp2) - 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

Dan T. Miller

2

Elizabeth N. Mason

3

Gregg O. Schneider

3

 

WEEK(date_exp) - 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

40

 

YEAR(date_exp) - Returns the year in date_exp as an integer value.

Example:

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

Returns:

CurYear

2004

 

QODBC System Functions

DATABASE() - 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

C:\Program Files\QODBC Driver for QuickBooks\sample_product-based.qbw

 

IFNULL(exp, value) - 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

Dan T. Miller

Missing Fax

Elizabeth N. Mason

480-134-1122

Gregg O. Schneider

Missing Fax

 

USER() - Returns the user's authorization name. This value is not used by QODBC but will return the value passed in on the connection string.

Example:

SELECT {fn USER()} AS "CurUser" FROM Company

Returns:

CurUser

TestUser

 

Functions can be nested

Example:

SELECT {fn LEFT({fn UCASE("Name")}, 5)} AS "LeftUCase", "Name" FROM Customer

Returns:

LeftUCase

Name

ABERC

Abercrombie, Kristy

2ND S

2nd story addition

2ND S

2nd story addition

2ND S

2nd story addition

155 W

155 Wilks Blvd.

75 SU

75 Sunset Rd.

 

Shared bottom Border

Did you find the answer you were looking for on this page? If not, please Contact Us. We are happy to help you find the solution you are looking for. Thanks!

Copyright © 2017 FLEXquarters North America Sales All rights reserved
QuickBooks and the Gold and Silver Developer Logos are trademarks and/or registered trademarks of Intuit Inc., displayed with permission. RSS What is RSS?