Toad World Blog

Working With Date Ranges in Toad Data Point

Apr 30, 2008 11:25:00 AM by Daniel Norwood

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.

DanielNorwoodBlog043008-1.gif-550x0

 

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.

/*Today*/

/*Current month*/

/*Yesterday*/

/*Last month*/

/*Current week*/

/*Current quarter*/

/*Last week*/

/*Last quarter*/

/*Last 7 days*/

/*Current year*/

/*Last 15 days*/

/*Last year*/

/*Last 30 days*/

 

 

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.

Tags: Toad Data Point

Daniel Norwood

Written by Daniel Norwood

Hi! I've been a product marketer, working with the Toad group. I've been working with our Toad products since 2001 and have even had the privilege to bring a few to market. When I'm not working, I'm usually either outside or with my family - preferably both! I currently live in sunny San Diego, CA with my wife and 3 little girls - and we're about to add a dog into the mix!