917.848.7284 Data for Finance and Accounting

Financial Reporting and Accounting Blog

SQL for Super Users Part 7: Date vs. Date-Time

Adam Jacobson May 11,2016

If you haven’t read all the other parts in this series, you’ll want to start with the introductory post.

I was initially unsure whether to include the topic of dates and date times in an introductory series. It tends to confuse the heck out of people.

But given how often mistakes are made, it’s an important topic. So let’s give it a go.

We’ll start with an example. Let’s go back to the Order table:

SQL_dates_1.png

You’ll notice the OrderDate column has more than just dates—it also has times. (I find this incredibly aggravating. If you don’t need time, don’t include time! But I digress.)

Since the time is always zero, we get the same results whether or not we include time in the WHERE clause. (Advanced users know that running the query without time is less efficient, but that’s a discussion for another day.)

Here’s a query without the time:

SQL_dates_2.png

And here’s a query with the time explicitly set to zero.

SQL_dates_3.png

But what if every row doesn’t have the time set to zero? (This happens often in inventory and point of sale tables. In these tables, it’s not enough to know the day something happened. You need to know the time something happened.)

Unfortunately, the sample Northwind database doesn’t have an example of such data. So I’m going to update one record by setting the time ahead one second:

SQL_dates_4.png

Now, watch what happens if I don’t include the time:

SQL_dates_5.png

We only get seven rows back.

But, if we fix the query to select the entire day, we get back all eight rows:

SQL_dates_6.png

And we can see the time in the last row is set to one minute after midnight.

So remember, when you have a date/time column, you need to consider whether time plays into it.

As a final step, let’s clean up the data:

SQL_dates_7.png

By the way, be careful when you use UPDATE. Hopefully, if you’re working on your company’s data, your system administrator only gave you select privileges. Otherwise, a few wrong keystrokes can cause a lot of damage.

I hope you’ve enjoyed this six part series on SQL for super users!

As mentioned in the intro post, if you’d like copies of sample documents that include the tables I created for some of these examples, let me know.

I’m also planning to create examples using sample data in NAV and GP. Again, let me know if you’d like a copy when they’re ready.

7 Keys to Cost Effective Business Intelligence

Tags: SQL