SQL

SQL and Date Functions

March 6, 2016

SQL has some really great features for managing dates. Given that dates are a very common thing to store in databases, that’s a pretty logical thing to get to a good, mature point. But the most challenging aspect of working with date and time in SQL is formatting. There are lots of different ways to format dates around the world, and even different structures (e.g. epoch) that are often complicated by adding time to records. But, you need to do things in a consistent fashion with how SQL expects them, if you’ll be using built in functions to manage dates. Dates can be stored in a variety of formats, which include the following:

  • DATE: Appears as YYYY-MM-DD
  • DATETIME: Appears as YYYY-MM-DD HH:MI:SS
  • TIMESTAMP: Appears as YYYY-MM-DD HH:MI:SS
  • YEAR: Appears as YY or YYYY

What are those functions?

  • CONVERT: Shows the date/time data in the specified format
  • CURATE: Shows the current date
  • CURTIME: Shows the current time
  • DATE: Extracts the date from a date/time field
  • DATE_ADD: Adds the time to a date
  • DATE_SUB: Removes the time from a date
  • DATE_FORMAT: Shows the date and time data in different formats
  • DATEDIFF: Shows the days between two specified dates
  • EXTRACT: Shows part of a date and time formatted value
  • NOW: Shows the current date and time, frequently used to capture the date during signups, etc

In this article, we’ll use the same “Customers” table from our first articles, but we’ll add a column for signupdate:

ID Site Contact Address City Zip Country SignupDate
1 Krypted Charles Edge my house Minneapolis 55418 US 2005-01-01
2 Apple Tim Cook spaceship Cupertino 95014 US 2015-12-05
3 Microsoft Satya Nadella campus Redmond 98053 US 2014-11-01
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 2010-03-10
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US 2016-01-01

Let’s show (SELECT) records with an SignupDate of 2005-01-01 from the table above.

SELECT * FROM Customers WHERE OrderDate='2005-01-01'

Overall, dates are pretty easy. Times are a little more challenging, and take up much more space in a database. But overall, provided you build the column with the right format you want to use, the functions make managing date tasks all the easier. I’m not going to cover all of the options, but let’s look at EXTRACT real quick. Here, we’ll serialize the year, month, and day from the previous select. To do so, we’ll use the SELECT statement, call the EXTRACT function, and then split the three parts of the date into variables we’re making up (so you can use your own strings for the names), as SignupYear, SignupMonth, and SignupDay:

SELECT EXTRACT(YEAR FROM SignupDate) AS SignupYear,
EXTRACT(MONTH FROM SignupDate) AS SignupMonth,
EXTRACT(DAY FROM SignupDate) AS SignupDay,
FROM Customers
WHERE OrderDate='2005-01-01'

Obviously, we knew the OrderDate, but we could have used any other column and pattern to match for that column in the WHERE to serialize date information based on other parameters. Fun stuff.