The latest shipping version of Drillbridge Plus (3.4.2+) has a few new tricks up its sleeves for advanced handling of date values coming back from your SQL query. 

Generally when you retrieve a date value with a relational query, the “right thing” happens automatically – i.e., the date value is displayed how you want it to be displayed. For those cases where you want more control over the output, you can use your database’s string/date formatting functionality to convert the date to a string and display it however you want. 

The slight problem here is what if the date doesn’t come back how you want it and you then format it how you want it, usually what has happened is that the DATE/TIME/DATETIME/TIMESTAMP column is now just a string (such as VARCHAR). This looks fine on the normal Drillbridge results page, but when you download your report as an Excel file, Excel will just think it’s a string as opposed to a date. 

So, for those cases where you want advanced control over how the date looks and you want to keep it as a date value when/if you download to Excel, there are now some advanced options for doing so. Reports now have two new options: Column date formats and Excel date formats.

First up is the Column date formats option. This option is a string that you can configure with a “map” value, where for any given column index you can specify a short Java date format string. 

For instance, consider this unformatted timestamp value that shows up in a Drillbridge report:

In this case due to the column type, we’re getting a full timestamp but we don’t actually want it. You might be thinking that we could just CAST/CONVERT to a DATE format but this doesn’t always work, actually (it varies by database driver). You can try it, of course, and it may give you the results you want. But let’s set a column date format using a Java date format string. In this case, we’ll set the value of the column date format to 3=yyyy-MM-dd. This map notation specifies that the third column (columns start at 1) should use the date format yyyy-MM-dd. If we had two columns we needed to format then we could write 2=yyyy-MM-dd, 3=yyyy-MM-dd, and so on. With this new date format in place and without any other changes to the query, the results now look like this:

We’re not quite dialed in yet though because as it stands right now, when we go to download this as an Excel file, it’ll look like this:

Since it’s a date column type, Drillbridge has indeed set it as such on the Excel file, but it’s not quite as perfect as it could be. Over on the Excel date format option (under Excel Options on the Drillbridge report options) we can specify a map of Excel formats to use. Note, however, that these formats are Excel date formats, and are a little different than Java formats. We can use the value of 3=mm-dd-YY (a date format with a two digit month, hypens, and a two digit year), for example:

And now when we go to download the Excel file, Drillbridge will use our format to generate output exactly the way we want.

As with most other options in Drillbridge, these ones are entirely optional but they give you some additional power and flexibility you can reach for when you need it.