SQL Current Date Minus 1 Day: Simple Yesterday Date Calculation

Yesterday’s date in SQL is one of those small things that trips up even experienced devs. I’ve seen it break reports and analytics when the syntax differs between systems.

MySQL wants CURDATE() – INTERVAL 1 DAY, while PostgreSQL prefers CURRENT_DATE – INTERVAL ‘1 day’. SQL Server uses DATEADD(day, -1, GETDATE()) for the same result.

Each database speaks its own dialect. Master these three patterns and you’ll handle 90% of real-world scenarios without reaching for documentation every time.

Between you and me, I still keep a cheat sheet. The moment you stop Googling these basics is when you know you’ve truly got it down.

What Youll Need

Step-by-Step Instructions for Calculating Yesterday’s Date in SQL

Here’s the simple, reliable method I use across all major database systems.

In SQL Server, the most straightforward approach is using DATEADD. I always write it as DATEADD(day, -1, GETDATE()) to subtract exactly one day from the current timestamp. This returns a datetime value including the time portion.

If you need just the date without time, wrap it in CAST or CONVERT: CONVERT(date, DATEADD(day, -1, GETDATE())). Verify the result shows only the date in YYYY-MM-DD format.

For MySQL, use CURDATE() – INTERVAL 1 DAY. This clean syntax returns the previous day in YYYY-MM-DD format. I prefer this over DATESUB as it’s more readable, though both work identically.

PostgreSQL uses similar syntax: CURRENTDATE – INTERVAL ‘1 day’. The quotes around the interval are mandatory. Test by running SELECT CURRENTDATE – INTERVAL ‘1 day’ and confirm it returns yesterday’s date.

For explicit formatting, use TOCHAR(TRUNC(SYSDATE) – 1, ‘YYYY-MM-DD’). This ensures consistent date string output regardless of session settings.

Here’s the thing—time zones can affect your result. In PostgreSQL, use CURRENTDATE instead of NOW()::date to avoid timezone conversion. The former is timezone-agnostic while the latter uses the session timezone.

Don’t just check the function in isolation. Test it in a query: SELECT * FROM orders WHERE orderdate = CURRENTDATE – INTERVAL ‘1 day’. This confirms the calculation works with your actual data schema.

I always run a sanity check by comparing with BETWEEN clauses: WHERE orderdate BETWEEN CURRENTDATE – INTERVAL ‘1 day’ AND CURRENTDATE. This should return the same records as the equality check for most use cases.

Tips and Common Mistakes

These insights will help you avoid common pitfalls when working with date calculations across different database systems.

Syntax Precision: In Step 2, remember that PostgreSQL requires quotes around the interval—INTERVAL ‘1 day’. Omitting these quotes will throw an error. I’ve seen developers waste hours debugging this simple oversight, so double-check before running your query.

Time Portion Handling: For SQL Server in Step 1, the DATEADD(day, -1, GETDATE()) function includes the current time. If you only need the date, wrap it with CONVERT(date, …) as mentioned. Without this, you might unintentionally filter out records due to time mismatches.

Oracle TRUNC Necessity: In Step 3, skipping TRUNC(SYSDATE) means you’ll subtract one day from the current timestamp, keeping the time portion. This can lead to unexpected results when comparing with date-only fields in your tables.

Verification Strategy: As Step 5 suggests, always test with real data. I recommend running both the equality check and the BETWEEN clause comparison to confirm your query behaves as expected across edge cases, like midnight timestamps.

Frequently Asked Questions

Is there a simpler way than INTERVAL?
In some databases, __PROTECT_9 works (e.g., SQLite). For Oracle, try PROTECT_10__. Always verify your database’s supported syntax.

Mastering SQL date calculations like current date minus 1 day unlocks powerful data insights. 💡 Now that you’ve learned the basics, try applying these techniques to real-world queries—like tracking daily trends or filtering records by date. Ready to level up? Explore more advanced date functions in your database system!