b¬äd†ødd

Dedicated to design and performance of databases and audio systems.

Querying Dates

Many times at conferences or reviewing SQL code I notice date criteria in a form like this example:

... where date_field = '2013/12/20'

Now, most every query that we write has some form of date criteria involved. We want metrics and results for a specific period of time, not the entire table. Therefore, we always use some restriction of time.

The problem is that while the above syntax works it is not efficient. Ultimately, with Teradata, SQL syntax that uses ANSI standard dates is the best.

Every date value is stored within Teradata as an integer value. We want Teradata to convert our dates, which we represent as character strings (e.g. 2012/12/20), as integer values instead; however, we still want to specify them as character strings. This way they are very efficient within our queries. And, efficient queries run faster, all other things being equal.

So, first, what is an ANSI date? One that looks like YYYY-MM-DD. Notice that the delimiters between year, month and day, are dashes, not slashes. Next, we want to tell the optimizer that our character string is to be interpreted as a DATE data type. That way it will explicitly perform the conversion of the character string to its internal numeric value.

Here's how the above example will now look

... where date_field = date '2013-12-20'

Try it and make it your standard practice. I have seen efficiency gains of 35%, but as always, your mileage may vary.

I hope this helps your queries be more efficient.

Cheers
-Brad