The CURRENT_DATE function in Snowflake

What is CURRENT_DATE in Snowflake?

CURRENT_DATE is a built-in function in Snowflake that returns the current date based on the session’s time zone. It is commonly used in date-based filtering, calculations, and reporting.

Syntax of CURRENT_DATE

SELECT CURRENT_DATE;

This query returns the current date in YYYY-MM-DD format.

Examples of Using CURRENT_DATE

1. Getting Today’s Date

SELECT CURRENT_DATE AS today;

Output: 2025-02-10

2. Filtering Data for Today’s Entries

SELECT * FROM orders WHERE order_date = CURRENT_DATE;

3. Calculating the Difference Between Dates

SELECT DATEDIFF(DAY, order_date, CURRENT_DATE) AS days_since_order FROM orders;

4. Adding or Subtracting Days

SELECT CURRENT_DATE + INTERVAL '7 DAYS' AS next_week;

SELECT CURRENT_DATE - INTERVAL '30 DAYS' AS last_month;

5. Formatting CURRENT_DATE

You can format the output of CURRENT_DATE using TO_CHAR:

SELECT TO_CHAR(CURRENT_DATE, 'Month DD, YYYY') AS formatted_date;

Output: February 10, 2025

Differences Between CURRENT_DATE and CURRENT_TIMESTAMP

Function	Returns	Time Component
CURRENT_DATE	Current date	No
CURRENT_TIMESTAMP	Current date & time	Yes
CURRENT_TIME	Current time only	Yes

If time precision is needed, use CURRENT_TIMESTAMP instead of CURRENT_DATE.

Best Practices for Using CURRENT_DATE

• Use CURRENT_DATE for Static Date Comparisons: Ideal for filtering records by date without a time component.
• Be Mindful of Time Zones: The returned date is based on the session time zone, which can be adjusted using ALTER SESSION SET TIMEZONE.
• Avoid Using CURRENT_DATE in Indexing Columns: Since CURRENT_DATE changes daily, queries with dynamic date filtering may not benefit from indexing.

Using Snowflake with Evidence

For teams working with date-based analytics in Snowflake, Evidence provides a seamless way to create automated reports and dashboards. With Evidence, you can:

• Integrate CURRENT_DATE-based filters in dynamic reports.
• Track trends and rolling date ranges for business intelligence.
• Automate recurring analytics workflows with SQL-driven reporting.

Explore more about using Snowflake with Evidence by visiting the Evidence documentation.