Date Night

For most couples, ‘date night’ is something to look forward to, but unfortunately does not come around often enough. For others, even the word ‘date’ sends a chill down their spine. It’s not an adverse reaction to their partner, but rather a unpleasant reminder to the daily toil that is working with DATE formatted data in their relational database.

Wow, that opening segue was more awkward than my first date with my wife…which is another story for another day.

Dates in the Database
Mmmmmm, dates. No, not those kind of dates!

For some folks, writing a query that requires DATE manipulation is just as hard as asking someone out on a first date. It shouldn’t be THAT hard of course – it is just a bit of math, syntax, and logic. If you are just a casual query writer, or you have multiple RDBMS platforms in your environment, then it can be hard to get to that comfort level.

Of course we have tools that make tedious tasks easier to stomach, and this challenge is no different. Toad has long made a big investment in working with dates easier to manipulate either inside your query or inside the database.

Exciting Enhancements in Toad for Data Analysts
Toad for Data Analysts is our query and reporting tool for all types of relational databases. If you spend a lot of time writing queries, browsing through tables, and answering business questions, then this is the ‘Toad’ that Quest has built just for you. We have spent a lot of time trying to make the ‘date’ puzzle more lie Tic-Tac-Toe and less like Soduku.

One example is our code snippets. Code snippets are frequently used SQL calls that we have gathered for Oracle, SQL Server, DB2, MySQL, and Sybase. You can access these via a docked panel or you can invoke them in the editor. I prefer the editor, as the docked panels have a tendency to steal more desktop real estate than I’m willing to give up.

And if I want to update a date value, then of course Toad gives me a calendar view that I can use instead of writing an UPDATE.

Working with Date Ranges
What I am really excited about now is the ability to code custom date ranges for my queries. As an example, let’s use the college system as an example. If I am a vendor that sells software (I know, what a stretch of my imagination!) to higher education entities like universities and colleges, then I must get very comfortable with their financials year calendar. You would think that any rational budgeting body would want to start in January and end in December, but we all know that rational thought and logic don’t really mix with our elected officials.

Some schools work on a June to June, May to May, or even August to August budget year. If I want to query data based on a financial quarter, then I get to do a good bit of dancing. Fortunately Toad is a very nice dance partner, unlike myself where I am prone to step on your toes.

Toad’s Query Builder has the ability to write a HAVING or WHERE clause that references a date range without you needing to write any of the SQL. Enhanced for version 2.7, Toad now allows you to modify these pre-programmed date ranges and even create your own!

When I reference an academic quarter, Toad allows me to configure the quarter periods.

How it Works
Toad generates your date code in the HAVING or WHERE clause. What you see is something like this

Before

 ...WHERE (Employee.BirthDate = calendar__academic__current_quarter__)

After

...WHERE (Employee.BirthDate  BETWEEN '2010-10-01 00:00:00' AND '2010-12-31 23:59:59.998') 

Presto-changeo! Magic? Not really, but it can save you a lot of time, especially if you are working with a specific set of date ranges.