Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Creating Up-to-the-Minute Reports

2:17 with Andrew Chalkley

One of the powerful features in SQL is writing queries based on today's date and time. We'll use a function to get today's date.

SQLite

To get the current date use: DATE("now")

To get the current time use: TIME("now")

To get the current date time: DATETIME("NOW")

MS SQL

To get the current date use: CONVERT(date, GETDATE())

To get the current time use: CONVERT(time, GETDATE())

To get the current date time: GETDATE()

MySQL

To get the current date use: CURDATE()

To get the current time use: CURTIME()

To get the current date time: NOW()

Oracle and PostgreSQL

To get the current date use: CURRENT_DATE

To get the current time use: CURRENT_TIME

To get the current date time: `CURRENT_TIMESTAMP

Cheat Sheets

One of the most powerful things you can do with SQL 0:00
is run queries based on today's date. 0:03
For example, sales totals from the beginning of the current month to today or 0:05
all users who've signed up on your site today. 0:10
But, you don't have to manually type in the current date 0:14
each time you create a query. 0:17
You can get today's date using a SQL function. 0:19
If you're running a query to get all of today's comments on a blog, or 0:22
retrieving the cells in the last week, you'll need to use the date function. 0:26
To get today's date from the date function, 0:32
you can pass in the string of now. 0:35
If today was the first of April 2020, we'd expect to see this. 0:37
If today was December 20th, 2019, we'd expect to see this. 0:43
Let's see this function in action. 0:49
This shows today's date. 0:52
We'll get different values because obviously, I'm recording this in the past. 0:54
Hello, you futuristic person, you. 0:58
Let's find all the orders that were placed today, but haven't been shipped yet. 1:00
When we review the orders table, we see that is a status. 1:06
Let's run a SQL query that will get all distinct statuses form the orders table. 1:13
We have placed and shipped, so we can search for all orders that are placed. 1:20
And then we want to search for ordered_on to equal the date now. 1:33
We can get this list of orders to the fulfillment staff, 1:50
so they can send out today's orders. 1:53
Whether you're a developer or analyst creating dashboard for 1:56
others to use, using the date function is incredibly useful. 1:59
In the next video, we'll see how we can run queries that will be able to generate 2:03
more interesting results. 2:08
And be sure to check out the teacher's notes to see the syntax 2:10
on how to get today's date in other versions of SQL. 2:13

You need to sign up for Treehouse in order to download course files.

Sign up