Automation – Date Ranges

I don’t know about you but I don’t particularly like dealing with dates in SQL. Unfortunately I don’t seem to be able to avoid them as most of my automated reports deal with a date range. In this posting I’ll show you what Toad for Data Analysts offers to help with date ranges.

Query Builder Date Range Tab

For the most common date ranges the Query builder can write the SQL for you. Just add the date column to your sql and click on the Where Condition row. This will bring up the Date Range tab. Select the date range you would like to use on the date column.

What is the Date Range Tab doing?

When you use the Date Range tab you will see the selection you added as a comment string. This will not execute normally like this. Toad for Data Analysts replaces this text string with the proper SQL just prior to executing. To see the SQL that is generated, go to the messages tab after you execute the SQ L. The statement as executed will display. Place your mouse over the SQL row and you can see the full SQL in the hint. Or if you want to copy and paste this SQL as a template for another SQL, right click and choose Text Output.

Query TabSQL

SELECT EMP.EMPNO, EMP.HIREDATE 
  FROM SCOTT.EMP EMP 
 WHERE (EMP.HIREDATE = '' /*Last week*/ )

 Automated SQL Executed (Oracle)

SELECT EMP.EMPNO, EMP.HIREDATE 
  FROM SCOTT.EMP EMP 
 WHERE (EMP.HIREDATE BETWEEN to_date('2010-01-01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') 
  AND to_date('2010-03-31 23:59:59', 'yyyy/mm/dd hh24:mi:ss')

Custom Date Ranges

What if you can’t use a common date range? To build a date range in SQL you will need to know what SQL functions are available for the database type you are working with. My favorite way to quickly learn what’s available is from the code snippets manager. Using the View Menu select ‘Code Snippets’. This will dock the Code Snippet manager. Toad for Data Analysts has bundled most of the common functions for each database. If you are using Oracle, find the Oracle section and then drill down to the Datetime Functions. If you click on the link at the bottom it will display a full reference for that function.

If you are using the Query tab of the Query Builder or an Editor you can drag the highlighted function to the editor pane and it will provide a template of the function. The input values are highlighted for your immediate entry.

If you are already familiar with the functions you want to use, you can use the formula tabs of the Where Condition editor or Calculated Fields editor.

Note: When you are using a query that joins two disparate databases (Heterogeneous Query) the functions will be ANSI SQL functions as you can only use functions common to both databases.

What about Automation?

What if you have a series of reports that run each month and you don’t want to change the date range in every SQL? In this case you can use automation variables. This can be done in two ways. You can use the automation variable and bind the value to the SQL statement or you can replace the value.

Oracle Example –
(Please visit the site to view this file)

Note: To run this sample place in C:Temp and change the Oracle connection to one defined in your Toad for Data Analysts. In this example the HR.JOB_HISTORY  table is used.

1)      If you are using an Oracle date range, first define two automation variables. I named them DATE1 and DATE2. Using the variable expression builder set the date values using the Date Functions. Press test to see that the value is in the correct format.

2)     Construct your SQL to use bind variables with the same names. Below is an Oracle sample where we use two date values to see all employees whose end date is within a specific time range.

SELECT JOB_HISTORY.EMPLOYEE_ID, JOB_HISTORY.END_DATE 
  FROM HR.JOB_HISTORY JOB_HISTORY 
 WHERE (JOB_HISTORY.END_DATE BETWEEN to_date(:DATE1, 'yyyy/mm/dd') AND to_date(:DATE2, 'yyyy/mm/dd'));

3) Now just add a ‘Select to File’ automation activity using the SQL in step 2. Export the results to an Excel file. Your script will look like this.

Toad Sample Database Example (Access) – (Please visit the site to view this file)

Some database connections cannot bind to multiple named variables. This occurs in Access, Excel and ODBC database connection types. We can still use date ranges here with variables. Instead of binding to the SQL we will use literal replacements.

Note: To run this sample place in C:Temp. You may need to change your connection to your installed Toad Sample Database as well as the SQL statement.

1)      Step one is the same as the Oracle example above.  First define two automation variables. I named them DATE1 and DATE2. Using the variable expression builder set the date values using the Date Functions. Press test to see that the value is in the correct format.

2)      In this example we are connecting to the Toad Sample Database which is an Access database. We cannot use unnamed parameters in automation (I haven’t written that enhancement yet) and we cannot use multiple variables (current limitation of the product). So we need to use these values and replace them in the SQL statement. To do this, build your SQL with a unique token for finding and replacing. I used the Oracle SQL*Plus symbol of ‘&’ followed by my automation variable names. But you can use anything.

SELECT ORDERS.ORDER_ID, ORDERS.ORDER_DATE |
  FROM `G:Program FilesQuest SoftwareToad for Data Analysts 2.5.1PluginsOdbcSample DatabaseQuest_Stage.mdb`.ORDERS ORDERS 
 WHERE (ORDERS.ORDER_DATE BETWEEN CVDATE('&DATE1') AND CVDATE('&DATE2'))

Note: The table name of the Toad Sample Database Orders table is a complete path to the Access file. The example above is the location where I have my Toad for Data Analysts 2.5.1 installed. You will need to change this path to point to your installation.

3)    Now add two find and replace activities. The first one will Find the ‘&DATE1’ string and replace it with the contents of the #DATE1# variable. (Remember to use the ‘#’ signs to dereference the variable.)  We need to copy the file to a different name because we are using the SelectDateRange.sql as a template. I copied the file to SelectDateRangeR.sql. Use this same file in the second Find and Replace activity.

4)     Now add your ‘Select to File’ activity using the SelectDateRangeR.sql and export to Excel file.   So there is your automated date range report. Adapt this to your needs.

I can’t say this will make working with dates fun, but at least they are workable.

Debbie