SQL has several built-in functions for manipulating date data in fields. Some of the more common ones are DATE_DIFF, DATE_ADD, NOW, and DATE_PART

For a full list of date functions see the reference section.

DATE_DIFF

DATE_DIFF returns the difference between two date or datetime values as a given unit of duration. It takes three arguments: the start date, the end date, and the unit of duration (one of “year”, “decade”, “century”, “quarter”, “month”, “week”, “day”, “hour”, “minute”, “second”, or “millisecond”).

If we wanted to know how many deals took more than 135 days to close in our deal pipeline we could run the query:

SELECT account,
       sales_agent,
       DATE_DIFF(created_date, close_date, "day") AS `days to close`
  FROM sales_pipeline
 WHERE DATE_DIFF(created_date, close_date, "day") > 135

And the results would look like:

We could also combine DATE_DIFF with AVG to find out the average number of days deals in the pipeline took to close:

SELECT AVG(DATE_DIFF(created_date, close_date, "day"))
  FROM sales_pipeline

DATE_ADD

DATE_ADD takes a date and adds a specified amount of time to it. As with DATE_DIFF, the time added can be any of the following: “year”, “decade”, “century”, “quarter”, “month”, “week”, “day”, “hour”, “minute”, “second”, or “millisecond”.

The number we got for average days to close from the last query can be combined with the DATE_ADD function to see which deals took longer than average to close:

SELECT account,
      sales_agent,
      DATE_DIFF(created_date, close_date, "day") AS `days to close`,
      created_date,
      close_date
 FROM sales_pipeline
WHERE DATE_ADD(sales_pipeline.created_date, 50, "day") < close_date

NOW

The NOWfunction returns the current date and time providing a timestamp to a query:

SELECT NOW()

NOW can also be combined with other date functions like DATE_ADD and DATE_DIFF. For example if we wanted to know the age in days of all the deals we have in progress we could run the following query:

SELECT account,
       sales_agent,
       DATE_DIFF(created_date, NOW(), "day") AS `days open`,
       created_date
  FROM sales_pipeline
 WHERE sales_pipeline.deal_stage = "In Progress"
       AND account IS NOT NULL

DATE_PART

DATE_PART is a function that returns a numeric value for part of a date or datetime value. Like DATE_ADD and DATE_DIFF, DATE_PART takes different units as one of its arguments. The unit types recognized by DATE_PART are “timezone”, “timezonehour”, “timezoneminute”, “year”, “decade”, “century”, “quarter”, “month”, “week”, “day”, “dayofweek”, “dayofyear”, “hour”, “minute”, “second”, and “millisecond”.

Though there are fields for create date and close date in our dataset, there isn’t one for the quarter in which deals were opened or closed. If we wanted to know in which quarters the biggest deals were closed, we could run a query like this:

SELECT account,
       sales_agent,
       DATE_PART("quarter", close_date) AS qtr,
       YEAR(sales_pipeline.close_date),
       close_value
  FROM sales_pipeline
 WHERE sales_pipeline.deal_stage = "Won"
 ORDER BY close_value DESC

The results of which look like:

Exercises (Continued from previous section)

Exercise 32

Write a query against the patients table that returns the patient, birthdate, and deathdate, and the patients age in years as age at death. The results should only include patients who have died, and they should be ordered by the age at death, oldest to youngest.

Try query