Snowflake Current Date: Efficient Date-Based Function

Date and time functions are crucial when extracting information from large datasets. Imagine a situation where you have to make marketing decisions based on seasonal trends and product performance. You need a report to check the product prices and marketing channels with the most traffic to make the marketing decisions. Here, date and time functions can be crucial in determining the best course of action.

Snowflake is a robust CRM platform offering various date/time functions that let you manage and operate on date-related data. These functions allow you to interact dynamically with data and produce accurate results. One of these functions is Snowflake current date. It lets you work with time-dependent data and helps you extract information according to your time settings. 

This article will give you an overview of Snowflake’s current date function, how it works, some practical use cases, and benefits.

What is the Snowflake CURRENT_DATE Function? 

Snowflake CURRENT_DATE function is an effective way to get the current date in Snowflake while working with SQL queries. You don’t have to define the parameters; the Snowflake current date automatically returns the value according to the user’s session time. The best case to use this function is when you must work with the current date while implementing queries. 

When you run the query using the current date Snowflake, the output is generated in the form of ‘YYYY: MM: DD’. The value returned is the same for both string and numeric values. The CURRENT_DATE Snowflake function also enhances your data management capabilities and is useful for filtering records, extracting historical data for generating reports, and more.

How Does a Snowflake Current Date Function?

It is important to understand the syntax and functionality of Snowflake’s current date function correctly to maximize its utilization. The function reflects the date on your system on your Snowflake server, which can slightly differ from your local time zone.

Let’s see how it works. 

Syntax of CURRENT_DATE Snowflake Function

CURRENT_DATE()

It is a straightforward function that does not require specifying the parameters. Since there is no need to define the parameters, retrieving the data from a table or database is also easy. This results in faster query performance. 

For example, if you want to confirm a date two days from the current date in Snowflake database, you can simply run the following query: 

SELECT CURRENT_DATE() + 2;

If the current date in your system is 2024:05:23, the result of the above query will be 2024:05:25.

Practical Examples and Use Cases of Snowflake Current Date Function

The current date in Snowflake ensures that your data operations are relevant. Here are some practical examples to help you understand the CURRENT_DATE Snowflake function more deeply. 

Example 1: Generate a Sales Report Using Current Date

You can use the CURRENT_DATE to retrieve sales data for a specific date using the following query: 

SELECT order_date from sales where order_date=CURRENT_DATE(); 

Here, the system will return all the sales data with the order_date as the CURRENT_DATE according to your system. 

Example 2: Retrieve Data for All the Events that Occurred Today Using the Current Date

You can use CURRENT_DATE to filter and retrieve data for all the events that occurred or scheduled today by executing the following command:

SELECT event_id, event_name, event_date from event where event_date = CURRENT_DATE();

In the above example, the query will provide an output listing the event ID, name, and date scheduled for today.

Example 3: Get the Current Month of the Year by Name Using the Current Date

Obtaining a specific month of the year allows you to study monthly reports, track the progress of your marketing campaigns, manage the billing cycle for subscriptions, and more.

You can get the current month’s name using the ‘TO_VARCHAR’ function alongside CURRENT_DATE.

SELECT TO_VARCHAR(CURRENT_DATE(), ‘Month’) AS current_month_name;

The above query will provide an output in the form of a table, with the column name current_month_name and the value ‘May’. 

These are some practical use cases of the CURRENT_DATE function in Snowflake. There are many other examples and uses for it. You can refer to the Snowflake documentation to better understand the date and time functions that can be implemented.

Considerations While Using the CURRENT_DATE Snowflake Function

There are some factors you should consider while using the CURRENT_DATE function.

• Timezone: The timezone parameter can affect your return value when using Snowflake’s current date. It produces output according to the time zone of your system session settings. Ensure that the timezone is ideally in sync with your reference time.
• DATE_OUTPUT_FORMAT: To maintain consistency in date format in Snowflake, you should adhere to a specific DATE_OUTPUT_FORMAT. For example, if you have set your DATE_OUTPUT_FORMAT to ‘YYYY: MM: DD’, all the dates will be displayed in this particular format, like 2024:05:23. 
• Parentheses: The CURRENT_DATE function in Snowflake can be used without parentheses, simplifying its use when calling queries. For example, you can run SELECT CURRENT_DATE to return today’s date. Although you can eliminate the parentheses while using the current date function, it is important to understand it in the context of your code. It gives you clarity for the query result.

What is CURRENT_TIMESTAMP?

The CURRENT_TIMESTAMP function of Snowflake returns the current timestamp for the system in the local timezone.

The syntax for this function is:

• CURRENT_TIMESTAMP( [ <fract_sec_precision> ] )
• CURRENT_TIMESTAMP

In the first syntax, <fract_sec_precision> represents the precision with which it reports the time value. By default, the precision value is nine nanoseconds.

For example, if you want to calculate the timestamp for today with a DATE_OUTPUT_FORMAT of YYYY-MM-DD HH24:MI:SS.FF, and where precision is set to 2, execute the following query:

SELECT CURRENT_TIMESTAMP(2);

The above query will return the value: 2024-05-23 15:30:38.29

If you change the TIMESTAMP precision for fractional seconds to four instead of two, the query will return the value 2024-05-23 15:30:39.2100.

The CURRENT_DATE and CURRENT_TIMESTAMP functions return values according to the system time zone settings. This can cause inconsistencies, such as aggregating data from different time zones or comparing data when working with varying storage systems on other devices. You can standardize the globally collected data using an ELT tool, such as Hevo, and ensure its accuracy and integrity.

Streamlining Snowflake Data Integration Using Hevo

Hevo is a real-time ELT platform that helps you address inconsistencies generated by differences in timezones while using the CURRENT_DATE function. The platform standardizes your timezone to UTC (universal time coordinated) when extracting and loading data from source to destination. 

Hevo also provides object-level control, enabling you to include data selectively or skip some objects during data ingestion. This allows you to optimize your data processing and partitioning data based on the date you want to filter current data from all the records. 

Among various other benefits, Hevo provides the automated schema feature, which reads the source’s data schema and replicates it to your destination. It helps you align your data correctly in the targeted systems by standardizing the timestamp of data to UTC during mapping.

You can also use the incremental data loading feature to capture and load the modified data into the targeted system. After you have processed data using CURRENT_DATE, it identifies the changes made and only loads new data, reducing the data transfer time. 

Besides this, Hevo offers two types of data transformation methods: Python-based and drag-drop transformations. These methods help clean data before transferring it. They also allow you to prepare your data with a consistent time format, ensuring data accuracy and consistency when ingested at the destination.

Overall, Hevo addresses the potential challenges and optimizes your data processing, ensuring the data is ingested efficiently and acutely.

How to Combine the CURRENT_DATE with Other Date Functions

Date and time calculations are the most critical to work with. You can combine the CURRENT_DATE function for performing calculations, Snowflake date comparison, retrieving date and time parts, and more. 

Let’s see some examples of combining CURRENT_DATE with other functions. 

Example 1: Retrieving Current Day of the Week 

You can get the current day of the week by combining EXTRACT and day-of-week functions with CURRENT_DATE.

Run the following query to get the current day of the week:

SELECT EXTRACT('dayofweek',current_date());

Example 2: Retrieving Date or Time Parts from Current Date 

You can get the date or time part of a date by combining the DATE_PART function with the CURRENT_TIMSTAMP.

Run the following query to get the current day of the month:

SELECT DATE_PART(day, current_timestamp());

Run the following query to get the current hour of the day:

SELECT DATE_PART(hour, current_timestamp());

Example 3: Calculating Dates and Time in a Business Calendar

You can get the date or time part of a date in a business calendar by combining the DATE_TRUNC and DATEADD functions with the CURRENT_DATE.

Run the following query to get the first day of the current month: 

SELECT DATE_TRUNC('month', current_date());

Run the following query to get the last day of the current month”

SELECT DATEADD('day',-1, DATE_TRUNC('month', DATEADD(day,31,DATE_TRUNC('month',current_date()) ) ) );

Limitations of Using Snowflake Current Date Function

• The CURRENT_DATE function is dependent on the timezone session. It can generate inconsistencies, such as mismatched data for a single report when different sessions are processed in different zones. 
• The output of CURRENT_DATE changes according to the time or day the function is called. Because of this, it is not possible to predict the behavior of queries, which can be problematic while testing a report or bugging a query. 
• When you have a global team, a change in date or time while handling scheduled jobs may pose challenges, such as making backups.

Benefits of Using Snowflake Current Date Function

• Efficient Query Performance: The current date in Snowflake allows you to retrieve data in real time based on your system session time. It makes calculations more accurate, improving query performance.
• Simple and Readable Format: The format and syntax of the CURRENT_DATE function are simple, easy to read, and understandable. 
• Compatible with Other Date/Time Functions: The CURRENT_DATE function is compatible with other date/time functions in Snowflake. It allows you to perform date-related tasks more efficiently. 
• Filter Data: You can filter your data by using the CURRENT_DATE function. It offers date-based filtering, which allows you to query data for date/time parts retrievals or perform data analysis.

Conclusion

The CURRENT_DATE function provides many benefits and utilities. It allows real-time access to date/time information and streamlines data management operations. Furthermore, the function is compatible with many other functions, which makes it rich in integration.

To transfer your processed data from a Snowflake database or data warehouse, you can use Hevo. It helps you quickly integrate your data with its no-code and automated data pipeline. All the changes made in the data are reflected correctly in your targeted system. The integration allows you to analyze your data and derive actionable insights.

FAQs 

Q1. How do you select days from the last seven days, where the value of the date is a date?

You can use the code below to select days from the last seven days, where the date value is a date.

WITH CTE_DATE 
AS ( 
SELECT 
CASE 
WHEN (DATECOL <> '' AND DATECOL is NOT NULL and DATECOL <> 'NULL') THEN TO_CHAR(TO_DATE(TO_CHAR(DATECOL), 'YYYYMMDD'), 'YYYY-MM-DD') 
ELSE '3000-01-01' 
END AS TEST_DATE,* 
FROM TABLENAME WHERE LEN (DATECOL) <> 1 ) 
SELECT * FROM CTE_DATE WHERE TEST_DATE > CURRENT_DATE()-7 ORDER BY TEST_DATE ASC

Q2. How do you display a CURRENT_DATE in full format?

You can display a CURRENT_DATE in full format using the following query: 

SELECT CURRENT_DATE as data;

Q3. How do you insert timestamp values in a Snowflake table?

You can insert timestamp values in the Snowflake table using the following query: 

INSERT INTO CDW_DB.LOAD_timestamp VALUES (Current_Timestamp)

Q4. How to add CURRENT_DATE in create script in a Snowflake table?

You can add CURRENT_DATE in the create script in a Snowflake table using the following query: 

SET table_name=(SELECT 'someNewTable_' || TO_VARCHAR(CURRENT_DATE(), 'YYYYMMDD')); CREATE TABLE IDENTIFIER($table_name) (col STRING);