Learn more about Toad for SQL Server
at Dell Software

Find solutions and downloads at the
Toad for SQL Server Support Portal

Toad for SQL Server 6.6

You are here: Edit SQL > Create and Edit SQL > Set Date Ranges

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 , 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 sales.ord_num, sales.ord_date
FROM
movies.dbo.sales
WHERE
(sales.ord_dateBETWEEN '2007-01-01 00:00:00'
                  
AND '2007-12-3100:00:00') 

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 sales.ord_num, sales.ord_date
FROM
pubs.dbo.sales sales
WHERE
(sales.ord_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 

of