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

Reporting with SQL

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

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    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 from 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

      You need to sign up for Treehouse in order to set up Workspace

      Sign up