Chat now with support
Chat with Support

Toad Data Point 5.0 - User Guide

Set Date Ranges

When writing a query, you frequently need to retrieve data from a range of dates such as the last week, month, or quarter. Identifying the actual date range to use is time consuming, confusing, and is often error prone.

Note: The date range format is specific to Toad. If you want to view the syntax of the date range so you can copy it to another application such as SQL*Plus, click the date range link in the Query tab, as illustrated in the following:

Scenario

You need to create a query that retrieves a list of the orders that were placed last year. In the past, you hard coded the dates to create the following statement:

SELECT ORDER_NUM, BOOKINGS_DATE
FROM
LICENSE_BOOKINGS
WHERE
(BOOKINGS_DATEBETWEEN to_date
('2007-01-01 00:00:00',
'yyyy/mm/dd hh24:mi:ss')
AND to_date
('2007-12-31 23:59:59',
'yyyy/mm/dd hh24:mi:ss'));                                                 

With Toad, you simply select Last year to dynamically insert the correct SQL. This ensures that your query is valid regardless of the date. The following displays the revised query: 

SELECT ORDER_NUM, BOOKINGS_DATE
FROM
LICENSE_BOOKINGS
WHERE
(BOOKINGS_DATE = '' /*Last year*/ ); 

The criteria is empty and contains two single quotes without a space. Toad inserts the correct SQL between these quotes when you execute the query.

The following date range commands are available and can also be used in the Editor:

/*Today*/

/*Last 15 days*/

/*Last quarter*/ 

/*Yesterday*/

/*Last 30 days*/

/*Current year*/

/*Current week*/

/*Current month*/

/*Last year*/ 

/*Last week*/

/*Last month*/ 

 

/*Last 7 days*/

/*Current quarter*/ 

 

Note: These commands are case sensitive.

To set a date range in the Query Builder

  1. Select a column with a date data type from a table in the Diagram tab.

  2. Select the Where Condition field below the date column and click .


  3. Review the following for additional information:

    Calendar

    Select the type of calendar to use for the date range values.

    If you select a calendar type other than Gregorian, you can click to edit the selected calendar. Review the following for additional information about the Calendar Editor:

    • Fiscal—For Fiscal calendar type, you can select Normal, or two four week periods and one 5 week period that can be defined as 4/4/5, 5/4/4, or 4/5/4.
    • Academic or Custom—For the Period name, updating the name updates the name value in the Date Range Values list.

    Notes: 

    • Click to view the dates.If you select a Custom calendar, you can customize the dates.
    • Click to delete a Custom calendar. If you click this for the Fiscal or Academic calendars, you are prompted to revert back to the original version.
    Date Range Values Select a date range.
  4. Add additional columns and complete the query.

Tip: You can save this Query Builder file (.tsm) and click Automate to schedule query execution, generate a report of the results, and email the report to colleagues. See Automate Tasks for more information.

 

Related Topics

Build Queries Visually 

Related Documents