KnowledgeSync "Did You Know?" -- Handy SQL Functions

In many issues of "KnowledgeSync News", we like to focus on a single technical aspect of the application that warrants a little extra explanation. This issue, the subject will be:

"Handy SQL Functions"

You may -- or may not -- already know these, but there are a few SQL functions that you can use when designing queries that really pump up the level of data analysis that you can perform. (Such as calculating the number of days before a lease is due to expire.)

All of these SQL functions can be used in any of the KnowledgeSync Query Designer tabs. After you choose a column (from the Columns, Sorting, Filters, or Sub-Filters tabs), click your mouse/cursor in the "Column Name" part of the grid and start typing. It's that easy.

Note that the following functions are all for use with SQL Server databases.

If you need to know the function for a different kind of database, just send an email to support@vineyardsoft.com and we'll do our best to find it what it is for that database.

1. DATEDIFF -- Calculating the number of days between two date values.

Format: datediff("timeunit",date1,date2)
Example: datediff("dd",getdate(),dbo.tarInvoice.DueDate)
Purpose: Identify A/R invoices that are coming due within 'x' days

2. DATEADD -- Adding 'x' days to a date/time field.

Format: dateadd("timeunit",number_of_units,datefield)
Example: dateadd("day",365,dbo.opportunity.closedate)
Purpose: Add one year to the sale date of a product.

(Note that "minute" and "hour" are also valid time units.)

3. SUBSTRING -- Retrieving only part of a field's value.

Format: Substring(table.column,starting position,length)

Example: Substring(account.postal,1,3)

Purpose: Retrieve the first 3 digits of a customer's postal code.

(Note that substring works only on character-based fields; to use the substring command on other types of fields requires that you "convert" the field to character first and then perform the substring command. See below)

4) CONVERT -- Change the format of a field.

Format: Convert(desired_format(length),field)

Example: Convert(char(20),account.createdate)

Purpose: Change the "create date" field from "date" to "string" format.

Do you have questions about (or interesting uses of) specific KnowledgeSync functionality? If so, please email Don Farber at farber@vineyardsoft.com.