Big SQL Energy Beginner Course: Lesson 17 Current Date & Time

Jan 17, 2025

Got BDE?⚡️

Hi BDEs! Welcome to Lesson 17 of the free Big SQL Energy Beginner Course⚡️ Today we’re learning about current date & time in SQL!

Return to Big SQL Energy Beginner homepage here to access the other lessons:

Return to Homepage 📚

If you’re new here:

💁🏽‍♀️ Who Am I?

I’m Jess Ramos, the founder of Big Data Energy and the creator of the BEST SQL course and community: Big SQL Energy⚡️. Check me out on socials: 🔗LinkedIn, 🔗Instagram, 🔗YouTube, and 🔗TikTok. And of course subscribe to my 🔗newsletter here for all my upcoming lessons and updates— all for free!

⚡️ What is Big SQL Energy?

🔗Big SQL Energy Intermediate is an intermediate SQL course designed to solve real-world business problems hands-on using realistic data and a modern tech stack. You’ll walk away with 2 SQL portfolio projects, portfolio building guidance, access to the Big Data Energy Discord community, and lots of confidence for you upcoming coding interviews! You also get access to all monthly events including masterclasses, office hours, and guest speakers. This course is all of the most important things I’ve learned on the job as a Senior Data Analyst in tech who grew from a $72K salary to over a $150K salary. And now I’m sharing it with YOU! It’s more than just another course— it’s a challenging program designed to upskill your SQL to the intermediate level, get ready to ace your live coding interviews, and build connections in the data community through the Discord and events.

JOIN now (pun intended 😉)

Lesson 17: Current Date & Time

🎥 Video

Now we’re stepping into some of the most disgusting parts of data analytics… dates 🤣 But it’s okay, if you survived the lesson on string manipulation, you’ll survive the next few lessons on dates— I promise! Dates are notoriously hard to work with since they have so many different formats and can upload strangely into databases sometimes. Don’t forget the lesson we learned on Cast() if you ever have data type issues with dates and date functions. Date functions require date inputs, so if your date isn’t reading as an actual date, you’ll probably get an error. We’ll learn more date functions in the upcoming lessons, but let’s first learn some date basics:

In SQL, we can calculate the current date, time, and datetime:

select 
    current_date, -- current date
    current_time,   -- current time
    current_timestamp, -- current date & time as timestamp
    now()             -- current date & time as timestamp
    --getutcdate()    -- works in some dialects but not in Hex
    ;

*** 🚨 ALERT! Please notice that the syntax for current date and time can very greatly between SQL dialects and environments. The first 4 methods shown in the select statement above work here in Hex, but the 5th line does not. Some SQL dialects use getutcdate() or get_utc_date() instead of current_timestamp. Also notice that some SQL dialects require parentheses at the end as part of the syntax like this: current_date(). Parentheses are NOT required in Hex, but I used them a lot out of habit. If you get errors here, ChatGPT and research the correct syntax for YOUR environment and dialect. 🚨

Being able to pull the current date and time in SQL helps us set dynamic filters in our queries. Hardcoding a date like this:

select 
   *
from 
   orders 
where 
   order_date >= '2023-08-03'

can cause our queries to quickly become stale and out of date as time passes on. A hardcoded date is forever frozen in time. Not only that, but if this query is in production or in a live dashboard, we’re constantly pulling in more and more data as time passes on without any sort of date limit or data reduction for optimization. We don’t want our dashboard pulling in tons and tons of old data which will only grow in size as time moves on with a hardcoded date like this. It’s best practice for optimization and live dashboards to use a dynamic date using the current date like this:

*** Notice the () are optional here in this dialect. I have a habit of using them since I started my career in SSMS which required the (). But you can skip them if NOT required by the environment you’re in.

-- with parentheses (some environments require them)
select 
    *, 
    current_date() - 7
from 
    orders 
where
    order_date >= current_date() - 7
    ;


-- without parentheses since not required in Hex
select 
    *, 
    current_date - 7
from 
    orders 
where
    order_date >= current_date - 7
    ;

The queries above pull only the orders that have happened in the last 7 days (i.e. orders that happened on or after 7 days before today).

The query below pulls only orders placed TODAY, and “today” will update based on the day the query is ran, which will give us constantly-updated results. This dynamic logic is better than hard-coded dates.

select 
    * 
from 
    orders 
where 
    order_date = current_date -- pulls order placed ON today
    --order_date < current_date -- pulls order placed BEFORE today
    ;

This query below calculates how many days ago the order was placed by subtracting the order date from the current date. We can also calculate this difference using DateDiff() which we’ll get to in a future lesson!

select 
    order_id,
    order_date,
    current_date(),
    current_date() - order_date as days_ago 
            -- ^^^ DYNAMICALLY UPDATES, good for dashboards
from 
    orders 
    ;

Current date and time can be extremely valuable for creating dynamic date filters and logic which are essential for dashboards and live queries in production! Keep that data FRESH and not STALE!

If you’ve enjoyed the free BEGINNER Big SQL Energy course, grab my intermediate course. There are no deadlines or timelines for this course— you start and take as long as you want. It will take your beginner skills to the next level and get you ready to ace your interviews. DM me or email me at courses@bdeanalytics.com if you have questions on the intermediate course!