Working with Datetime Values Using CData Drivers

Enterprise reporting and analytics are commonly reliant upon dates and times, whether a business is compiling quarterly reports or an analyst wishes to visualize profit changes over the past year. The CData Drivers offer comprehensive support for working with dates and times in SQL queries, from allowing users to easily query data for a given period of time to enabling users to configure the locale for the dates and times in a specific query. This support is enabled/featured/populated across all 120+ data sources. This article walks through the specific features that are available and provides sample SQL queries for working with data based on specific dates and times.

Datetime Columns in SQL Queries

For most supported data sources, CData drivers are able to determine which fields for a given entity are considered dates/times based on dynamic metadata querying. For those data sources without strict typing (or no typing at all), the drivers will typically determine which fields are dates/times by scanning a sample of the existing data.

Use Parameterized Queries When Possible

It should be noted that users generally will have a better experience if they use parameters when programmatically building SQL functions that involve datetime columns, particularly using built-in objects with date or datetime types in the development environment of their choice. In this way, users will avoid issues related to time zones, datetime formats, and other common issues. With that said, the CData drivers offer robust support for working with Datetime columns.

Supported Features

• Any datetime values returned by the drivers are represented in local time.
• Unless the time zone is specified in a datetime literal (i.e. '12/25/2018T00:00:00-04:00'), the current locale/time zone is used.
• Date literal functions are supported, from the simple TODAY() to the more complex NEXT_N_WEEKS(n).
• Date functions that manipulate a date or parse out a date part are supported, like DATEADD(datepart, integer, date) or DATEPART(datepart, date).

Sample Queries

Below are several sample queries for working with datetimes in SQL queries from various supported sources, along with a brief explanation of how the query is translated.

Collect HubSpot Tasks Started During the Current Quarter

SELECT * FROM HubSpot.Tasks WHERE HubSpot.Tasks.StartDate = THIS_QUARTER()

The criteria for this query is translated into two separate filters linked with an AND operator:

1. Dates greater than or equal to the first day of the current quarter
2. Dates less than the first day of the next quarter

For example, if today's date is July 10, 2018 the filter is: StartDate >= 07/01/2018 AND StartDate < 10/01/2018

Collect New Salesforce Opportunities from Last Week

SELECT * FROM Salesforce.Opportunity WHERE Salesforce.Opportunity.CreatedDate = LAST_WEEK()

The criteria for this query is translated into two separate filters linked with an AND operator:

1. Dates greater than or equal to the first day of last week
2. Dates less than the first day of the current week

For example, if today's date is July 10, 2018 the filter is: CreatedDate >= 07/01/2018 AND CreatedDate < 07/08/2018

Collect QuickBooks Online Accounts Last Modified in November of the Previous Year

SELECT * FROM QuickBooksOnline.Accounts WHERE QuickBooksOnline.Accounts.MetaData_LastUpdatedTime = LAST_YEAR() AND DATEPART('m', QuickBooksOnline.Accounts.MetaData_LastUpdatedTime) = 11

The criteria for this query will be translated into three separate filters, linked with AND operators:

1. Dates greater than or equal to the last day of TWO years ago
2. Dates less than the first day of the current year
3. Dates whose month part is equal to 11

Query Push-Down

Using the above query, the driver pushes down the filters based on the year, but QuickBooks Online does not support filtering by month part. If today's date is July 10, 2018, the filter pushed down is MetaData_LastUpdatedTime >= 12/31/2016 AND MetaData_LastUpdatedTime < 01/01/2018

Client-Side Filtering

CData Drivers use client-side processing to handle filters and other query functionality that are not supported server side. The results based on the filter above will be processed in-memory and only those Accounts which were updated in the month of November will be returned to the tool or application leverage the CData driver.

Free Trial & More Information

Not only do CData drivers provide a standards-based SQL interface for 120+ SaaS, Big Data, and NoSQL sources, but they all include support for comprehensive datetime functionality, from filtering to parsing. Download a free, 30-day trial to experience the CData difference for yourself. As always, our world-class support team is ready to answer any questions you may have.