Working With Date Ranges in Toad Data Point

Often when writing a query you need to get data from a range of dates; when you start thinking about automating the SQL query it’s even more important. In this post I’ll show you a quick trick to let Toad Data Point figure it out for you! 

For this example I’ll be working with Oracle though it’s no different for other platforms. In my Oracle database, if I wanted to get a list of the orders that were placed last year I’d have to write something like the following:

SELECT ORDER_NUM, BOOKINGS_DATE
 FROM LICENSE_BOOKINGS
 WHERE (BOOKINGS_DATE BETWEEN 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'));

Sure, some people are familiar with the syntax. But what about the syntax for the last quarter, the current month or just last week? It’s not rocket science, but it can get confusing and is quite error prone.

Now, in the SQL above, I just hard coded the dates – that’s a dangerous practice in most cases. So what do I do? I start thinking about maybe writing it a little bit differently… maybe you use a bind variable or I start working with SYSDATE to get the system’s date and first and work backwards from there. If I want to automate the query, this is the path that I have to go down.

In Toad Data Point we make life a little bit easier – we write the date ranges for you. So now instead of using complicated SQL to get the job done, you can drop in something like /*Last week*/ or /*Last 30 days*/ and Toad Data Point will dynamically insert the correct SQL. Keep in mind that this is a Toad Data Point feature – if you execute the SQL statement in something like SQL*Plus the Oracle compiler will see /*Last week*/ as a comment and your statement will not run.

Here’s how the revised query will look:

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

(Note that the criteria is empty – I use two single quotes with no space. This is important. Toad Data Point will insert the correct SQL between these two single quotes when I execute the query.)

If you use the Query Builder in Toad Data Point it’s super-simple. Once you select a column with a date datatype, click on the “Where Clause” field to open a new window where you pick from a list of available ranges. Viola! You’re done.

For those of you writing SQL scripts by hand, here’s a list of the available commands. Keep in mind that they’re CaSe SeNsItIvE… if you don’t write it exactly as you see here then it won’t work.

If you want to automate the script you’ve created, save it to disk and then click “Automate” on the Toad Data Point toolbar. After you build the automation routine you can click “Schedule” to add it to the Windows Scheduler and set it to run anytime.

Useful resources

Video: Top 5 reasons to buy Toad Data Point Professional Edition, a solution for simplifying data access, integration, and provisioning.

Case study #1: Dell: Enterprise financial group solves data prep challenge.

Case study #2: Opening doors and creating opportunities with data insights.

Related blogs

Access more Toad Data Point blogs.

Got questions?

If you have any questions, please post questions to the Toad Data Point forum on Toad World.