How to Query Date and Time in Snowflake

Snowflake offers a variety of methods to handle date and time data. From simple date retrieval to complex time zone conversions, Snowflake provides flexible and efficient options to manage these types of data. In this tutorial, we will walk you through different date and time functions in Snowflake that can help you easily query, manipulate, and format time-based data.

1. Retrieving the Current Date and Time

To get the current date and time in Snowflake, you can use the following SQL functions:

• CURRENT_DATE() – Retrieves the current date.
• CURRENT_TIME() – Retrieves the current time.
• CURRENT_TIMESTAMP() – Retrieves the current timestamp, including the date and time.

SELECT CURRENT_DATE();

SELECT CURRENT_TIME();

SELECT CURRENT_TIMESTAMP();

2. Formatting Date and Time

Snowflake provides a TO_CHAR function to format date and time values according to a specified format. Here’s how you can use it:

SELECT TO_CHAR(CURRENT_DATE(), 'YYYY-MM-DD');

This would return the date in the format 2025-05-07.

3. Date Arithmetic

You can easily perform date arithmetic using Snowflake’s date functions. For example, adding or subtracting days from a date:

SELECT DATEADD(DAY, 7, CURRENT_DATE());

This query will return the date 7 days after the current date.

4. Working with Time Zones

Snowflake supports the CONVERT_TIMEZONE function to handle time zone conversions. For instance:

SELECT CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', CURRENT_TIMESTAMP());

This will convert the current UTC timestamp to Los Angeles time.

5. Extracting Parts of a Date

If you need to extract specific parts of a date or time (e.g., year, month, day, etc.), you can use the EXTRACT function:

SELECT EXTRACT(YEAR FROM CURRENT_DATE());

This will return the current year.

Conclusion

Snowflake makes querying and manipulating date and time data simple and intuitive. By using functions like CURRENT_DATE(), TO_CHAR(), DATEADD(), and CONVERT_TIMEZONE(), you can handle almost any date and time requirement. These functions are essential for working with time-sensitive data in your Snowflake database.