How CURRENT_DATE works in SQL? Best CURRENT_DATE examples

How CURRENT_DATE works in SQL? Best CURRENT_DATE examples

When working with SQL, handling dates is a fundamental skill. One function that comes in handy quite often is CURRENT_DATE. This function allows you to fetch the current date directly from your database system. But how exactly does it work? In this article, I’ll break down everything you need to know.

What is CURRENT_DATE in SQL?

CURRENT_DATE is a built-in SQL function that returns the current date as per the database server’s system date. It does not include the time component, making it different from functions like NOW() or CURRENT_TIMESTAMP, which provide both date and time.

Key characteristics of CURRENT_DATE:

  • Returns only the date (YYYY-MM-DD format in most database systems).
  • Depends on the database server’s system date.
  • It is evaluated once per query, meaning all rows will have the same date in a single query execution.

Basic Usage of CURRENT_DATE

The syntax for CURRENT_DATE is simple:

SELECT CURRENT_DATE;

Depending on the database system in use, you might need to alias the output for clarity:

SELECT CURRENT_DATE AS today_date;

CURRENT_DATE vs. Other Date Functions

Let’s compare CURRENT_DATE with other SQL date-related functions:

Function Output Format Includes Time? Use Case
CURRENT_DATE YYYY-MM-DD No Fetch current date
CURRENT_TIMESTAMP YYYY-MM-DD HH:MI:SS Yes Fetch current date with time
NOW() YYYY-MM-DD HH:MI:SS Yes Fetch current date and time (commonly in MySQL/PostgreSQL)
GETDATE() YYYY-MM-DD HH:MI:SS Yes Fetch current date and time (SQL Server)

Practical Examples of CURRENT_DATE

To better understand the function, let’s go through some useful use cases.

1. Getting Today’s Date

SELECT CURRENT_DATE;

This will return the current date, for example:

2024-06-12

2. Filtering Records by Current Date

If you have a table named orders and you want to get all orders placed today, you can use:

SELECT * FROM orders WHERE order_date = CURRENT_DATE;

3. Adding or Subtracting Days

You can perform date arithmetic with CURRENT_DATE:

  • Find yesterday’s date:
SELECT CURRENT_DATE - INTERVAL '1 day'; -- PostgreSQL
SELECT DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY); -- MySQL
  • Find next week’s date:
SELECT CURRENT_DATE + INTERVAL '7 days'; -- PostgreSQL
SELECT DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY); -- MySQL

4. Getting Month or Year from CURRENT_DATE

If you need to extract parts of the current date, you can use:

SELECT EXTRACT(YEAR FROM CURRENT_DATE); -- Returns year
SELECT EXTRACT(MONTH FROM CURRENT_DATE); -- Returns month

Database Compatibility

CURRENT_DATE is widely supported across different SQL database systems. However, syntax for date operations may vary slightly:

  • MySQL: Uses CURRENT_DATE or CURRENT_DATE() interchangeably.
  • PostgreSQL: Uses CURRENT_DATE without parentheses.
  • SQL Server: Uses GETDATE() instead of CURRENT_DATE.
  • Oracle: Uses SYSDATE instead of CURRENT_DATE.

Best Practices for Using CURRENT_DATE

  1. Use CURRENT_DATE in queries where you only need the date portion, avoiding unnecessary time value comparisons.
  2. When dealing with time zones, ensure your server time settings are correct to avoid incorrect date values.
  3. If working in SQL Server or Oracle, use their respective equivalents like GETDATE() or SYSDATE as required.
  4. Always perform necessary timezone conversions if needed to match user expectations.

Conclusion

Understanding how CURRENT_DATE works in SQL is essential for managing date-related queries efficiently. Whether you’re filtering results, performing date calculations, or extracting date components, this function provides a straightforward way to work with the current date. Hopefully, these examples have helped illustrate its versatility.

 

Other interesting article:

How NOW works in SQL? Best NOW examples