Current Date & Time in SQL: Lesson 17 Free Beginner SQL Course

A super confusing topic broken down simply!

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!

🚨 15 more days of 20% off my intermediate course!

This will be the last big sale for AWHILE! So take your happy new year gift. You’ll be ready for it after you finish this beginner course 🎉 And after intermediate, you’ll interview and job READY 🔥

---

If you’re new here, 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.

🔗Big SQL Energy Intermediate is an intermediate SQL course designed to solve real 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. 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!

Use code Jan2025 for 20% off for all of January!

Free Beginner Big SQL Energy course: The next lesson for you is below! ⬇️

---

Miss the intro and setup? ⬇️

🎥 Intro Video

✉ Intro & First Lesson

---

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!

Amazing learnings! I’ll see you in the next lesson! But first…

🚨🚨🚨 Don’t MISS OUT on this 20% off discount for my intermediate course! This beginner course is a good intro, but it will NOT get you interview and job ready! 🚨🚨🚨

DM me or email me at courses@bdeanalytics.com if you have questions on the intermediate course!

-xoxo,

jess💕