"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:
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
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
firstname.lastname@example.org and we'll do
our best to find it what it is for that
1. DATEDIFF --
Calculating the number of days between
two date values.
Purpose: Identify A/R invoices that are
coming due within 'x' days
2. DATEADD --
Adding 'x' days to a date/time field.
Purpose: Add one year to the sale date
of a product.
"minute" and "hour" are also valid time
3. SUBSTRING --
Retrieving only part of a field's value.
Retrieve the first 3 digits of a
customer's postal code.
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.
4) CONVERT --
Change the format of a field.
the "create date" field from "date" to
Do you have
questions about (or interesting uses of)
specific KnowledgeSync functionality? If
so, please email Don Farber at email@example.com.