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! 

MediaBanner-ToadWorld-600x100-IM-JY-63709.psd-1-3-1-2

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.

 

 

Free trial

Toad Data Point, is a powerful tool that will help you access and prepare data for faster business insights. Toad Data Point enables business or data analysts to seamlessly access more than 50 data sources—both on premises and in the cloud—and switch between these data sources with near-zero transition times. Users can connect, query and prepare data for faster business insights. 

Whether you are currently a Toad Data Point customer or just getting started with our free 30-day trial, learn more and access the Toad Data Point User Guide.

If you're in a trial and Toad Data Point is helping you access and prepare data for faster business insights, buy it now or contact a sales representative.

 

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.

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!