DATE and DATETIME

Dates and times are extremely frustrating data types and SQL doesn’t make them any easier to work with than other languages. While date and time are actually completely different data types, they are often merged into a datetime data type. SQL dates and times alone are pretty simple even though they require a special formatting. However, merging the two can be one of the most painful tasks you will face in your early years. A good rule of thumb is to only use a time and date together when you absolutely need to or will need to in the future. I’ll explain why dates and times complicates things in just a minute. First, let’s look at how easy life when you simply work with dates.

Working With SQL Dates

Our “Users” Table:

Let’s try to get all of our members before December 25, 2011.

SELECT *
FROM Users
WHERE member_since < '2011-12-25'

Alright, we said BEFORE December 25, 2011, which means poor Hillary isn’t included because she is on that day. Al barely made the cut, but old George was way before our cut off date. I suppose we should actually talk about what happened. Up to WHERE member_since < ‘2011-12-25’, we know what were doing. We could probably guess what is going on here, but I’ll beat the dead horse. We check our member_since column to see if it is less than, <, our date, which was written in single quotes as ‘2011-12-25’. Often when googling for help with dates, you see this syntax referred to as ‘YYYY-MM-DD’, where Y is for year, M is for month, and D is for day, obviously. Each repetition is a symbol of a blank character space for that element. So, you only want 2 digits for month because that makes sense, and maybe you don’t care about the first 2 characters of year. Instead you just write, YY to get 11. Easy enough, onto the wonderful DATETIMEs

Using DATETIME in SQL

Our New “Users” Table:

Now, we attempt to get all of our members that logged in before before 1:00PM December 24, 2011.

SELECT * 
FROM Users
WHERE last_login < '2011-12-24 13:00:00'

Poor George… Alright, exactly the same syntax as a last one with except we replaced the column name and used a DATETIME string to compare against. The DATETIME data type contains all of the same elements as the DATE data type plus a time string. Commonly referenced as ‘YYYY-MM-DD HH:MM:SS’ Again, in single quotations and with a space separating the date string from the time string, we create our DATE string for comparison. Hillary and Al both make the cut because they logged in before the ‘2011-12-24 13:00:00’ cut off DATETIME. It’s much easier here than it looks in practice. That’s because we put in the DATETIME string by hand. SQL has a few DATE functions you can play around with that you can replace our string with:

Important SQL DATE Functions

Note: SQL DATE functions differ greatly across SQL versions. These are MySQL.

• CURDATE() – gets the current date
• CURTIME() – gets the current time
• NOW() – gets both the date and time (in DATETIME format)

If you are not using MySQL, consider switching. Just kidding, kind of, but no worries, a simple query of Google will help you find the right SQL functions for your SQL database.