KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: Filtering on a DATE field through ODBC in Excel
PRODUCT: 4D ODBC | VERSION: 11.4 | PLATFORM: Mac & Win
Published On: November 19, 2009
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

When using Excel to pull data from a 4D data source over ODBC one may find themselves filtering based on data values. If you are filtering on a DATE field you should be aware that when accessed via ODBC or SQL, the DATE field is treated as a SQL Timestamp field. As such, you should encapsulate your query string in one of the following timestamp formats:

  1. Use the ts notation for timestamp like so:
    {ts '2009-11-05'}


  2. Encapsulate the date in pound signs like so:
    #11/05/2009#

Note: a true time stamp should include both a date and a time, the examples above leave out the time, but still work as of this writing. The following time stamp example includes the time and also works:

{ts '2009-11-05 00:00:00'}