Working with dates and times is a common task in data analysis and reporting scenarios. Whether you’re analyzing time-series data, generating reports based on specific date ranges, or performing complex date calculations, having the right tools and functions at your disposal is crucial.

The Snowflake DATE_PART function is an incredibly powerful tool for working with dates and times. In this blog, we will delve into the intricacies of the DATE_PART function, exploring what it is and how it works. We will also discuss the use cases and examples to help you understand its functionality. 

What is DATE_PART Snowflake?

The DATE_PART function in Snowflake is a versatile tool that allows you to extract a specific date or time part from a date, time, or timestamp expression. It’s particularly useful in data analytics and reporting, where precise date manipulations and extractions are often required.

How does a Snowflake date_part function work? (to include the syntax, arguments and returns)


The syntax for the function is as follows:

DATE_PART( <date_or_time_part> , <date_or_timetamp_expr> )
DATE_PART( <date_or_time_part> FROM <date_or_time_expr> )


The DATE_PART function in Snowflake takes two arguments:

  1. <date_or_time_part> 
  • This argument specifies the unit or component of the date or timestamp that you want to extract.
  • It must be one of the supported date and time part values such as ‘year’, ‘month’, ‘day’, ‘hour’, ‘minute’, ‘second’, etc.
  • The value can be provided as a string literal enclosed in single quotes (e.g., ‘month’) or as an unquoted identifier (e.g., month).
  • For certain part specifications like ‘week’, ‘dayofweek’, or ‘yearofweek’, the output is influenced by the WEEK_START session parameter, which determines the start day of the week.
  • Additionally, when using ‘dayofweek’ or ‘yearofweek’, the output is also controlled by the WEEK_OF_YEAR_POLICY session parameter, which defines how weeks are numbered within a year.
  1. <date_or_timetamp_expr>
  • This argument is the actual date, time, timestamp, or an expression that can be evaluated to a date, time, or timestamp value.


A Snowflake DATE_PART function always returns a NUMBER type value.


Let us look at some examples to understand the functionality of the Snowflake date part.

Example 1: Extracting Part of a Date

SELECT DATE_PART(quarter, '2024-05-08'::DATE);


| DATE_PART(QUARTER, '2024-05-08'::DATE) |


|                                      2 |


Example 2: Extracting Part of a Timestamp


  '2024-05-08T22:49:20.123-07:00') AS "TIME_STAMP1",



| TIME_STAMP1             | EXTRACTED YEAR |


| 2024-05-08 22:49:20.123 |           2024 |


Example 3: Converting a Timestamp to the Number of Seconds


 '2024-05-08T22:49:20.123-07:00') AS "TIME_STAMP1",





| 2024-05-08 22:49:20.123 |             1712619560 |


Example 4: Converting a Timestamp to the Number of Milliseconds


  '2024-04-08T23:39:20.123-07:00') AS "TIME_STAMP1",





| 2024-04-08 23:39:20.123 |               1712619560123 |


Use Cases of date_part Snowflake

Use Case 1: How to Filter Data based on specific Date components?

Suppose you have table orders with a column order_date containing timestamps. You can filter the data based on a specific year or month using DATE_PART:

-- Get orders from the year 2022


FROM orders

WHERE DATE_PART(year, order_date) = 2022;
-- Get orders from March 2022


FROM orders

WHERE DATE_PART(year, order_date) = 2022

  AND DATE_PART(month, order_date) = 3;

Use Case 2: How to group and aggregate Data by Date Components?

You can group and aggregate data by year, month, or any other date component to analyze trends or patterns:

-- Get total sales by month and year

SELECT DATE_PART(year, order_date) AS order_year,

       DATE_PART(month, order_date) AS order_month,

       SUM(total_amount) AS total_sales

FROM orders

GROUP BY DATE_PART(year, order_date), DATE_PART(month, order_date)

ORDER BY order_year, order_month;

Use Case 3: How to perform data calculations and manipulations with DATE_PART?

DATE_PART can be used in conjunction with other date functions to perform calculations and manipulations:

-- Calculate the number of days between two dates

SELECT DATEDIFF(day, '2023-01-01', '2023-05-15') AS days_diff;
-- Calculate the age in years from a birth date

SELECT DATE_PART(year, CURRENT_DATE()) - DATE_PART(year, birth_date) AS age

FROM customers;

Use Case 4: How to generate date ranges & Intervals using DATE_PART?

You can use DATE_PART to extract all the dates in a particular month or particular days in a month:

-- Get all dates in the month of March 2023

SELECT DATE_FROM_PARTS(2023, 3, day_number) AS march_dates

-- Get all Mondays in 2023

SELECT DATE_FROM_PARTS(2023, month_num, day_num) AS mondays_2023

FROM TABLE(FLATTEN(SPLIT_RANGE(1, 13), year_num => 2023, month_num => seq8(), day_num => seq8()))

WHERE DATE_PART(dayofweek, DATE_FROM_PARTS(2023, month_num, day_num)) = 2;

Supported Date and Time Parts in the DATE_PART Snowflake function

The Snowflake date part function can accept the following date or time part(case sensitive) in their arguments:

Date PartsAbbreviations/ VariationsDate PartsAbbreviations/ Variations
yeary , yy , yyy , yyyy , yr , years , yrshourh , hh , hr , hours , hrs
monthmm , mon , mons , monthsminutem , mi , min , minutes , mins
dayd , dd , days, dayofmonthseconds , sec , seconds , secs
dayofweekweekday , dow , dwnanosecondns , nsec , nanosec , nsecond , nanoseconds , nanosecs , nseconds
dayofweekisoweekday_iso , dow_iso , dw_isoepoch_secondepoch , epoch_seconds
dayofyearyearday , doy , dyepoch_millisecondepoch_milliseconds
weekw , wk , weekofyear , woy , wyepoch_micrpsecondepoch_microseconds
weekisoweek_iso , weekofyeariso , weekofyear_isoepoch_nanosecondepoch_nanoseconds
quarterq , qtr , qtrs , quarterstimezone_hourtzh

Benefits of DATE_PART Snowflake function 

  • Versatility: The Snowflake date_part function is versatile and supports a wide range of date and time parts(as seen in the table above), simplifying data analysis and time bases calculations.
  • ISO Week functionality: date_part Snowflake function follows the ISO week semantics, ensuring consistent behaviour across different date and time parts.
  • Integration with Other Functions: The DATE_PART function can be combined with other Snowflake functions, such as DATEDIFF, DATE_TRUNC, or DATE_FORMAT, to perform more complex date and time calculations or transformations.

Limitations of DATE_PART Snowflake functions 

  • Limited support for Date/Time parts: The date_part function in Snowflake only supports pre-defined data and time parts. You cannot create a custom unit or create your own calculation within the function.
  • Limited Functionality: The date_part Snowflake function cannot perform complex functions such as adding or subtracting dates, calculating differences between dates, or formatting time/date. 
  • Missing Timezone Awareness: date_part doesn’t inherently handle time zones. If your data includes timestamps with time zones, you might need to perform additional processing to ensure consistent results based on the desired time zone.

Simplify Time-based analysis and ELT in Snowflake using Hevo

Efficient time-based analysis becomes streamlined when your organization’s data is consolidated into Snowflake. Snowflake’s DATE_PART function provides robust support for date and time-related calculations and analysis. To cost-effectively automate the data migration process to Snowflake, consider leveraging Hevo Data, a real-time, no-code ELT data pipeline platform with 150+ connectors. Hevo Data offers seamless integration with Snowflake and the Snowpark API, facilitating an efficient data consolidation process. Additionally, Hevo Data’s key features enhance the utilization of Snowflake’s DATE_PART function:

  • Data Transformation: Hevo Data offers user-friendly, drag-and-drop transformations, including Date and Control functions. These transformations enable the removal of inconsistencies in date or time formats before loading data into Snowflake. This ensures the smooth execution of DATE_PART functions in your date/time-related calculations.
  • Incremental Data Load: Hevo Data facilitates incremental data transfer, allowing you to apply the DATE_PART function on data as soon as it arrives in Snowflake. This capability ensures that your date-based analytics and reports are always up-to-date, leveraging the latest available data for accurate time-based calculations using the DATE_PART function.
Solve your data replication problems with Hevo’s reliable, no-code, automated pipelines with 150+ connectors.
Get your free trial right away!


This is all about the DATE_PART Snowflake function. With this Snowflake function, you can extract particular dates, times and timestamps, calculate the last day of a month, add days to a specific date, extract the Mondays of a year, calculate the gap between two dates and multiple other things. 

These calculations are an integral part of the data analysis workflow and are important for time-series analysis, data aggregation, and any scenarios where you need to work with particular times and dates. 

While date and time manipulation in Snowflake becomes easy with DATE_PART, scattered data can become a hindrance here. A no-code ELT platform like Hevo Data can consolidate all your data from your sources into Snowflake in a few easy steps and allow you to perform easy date and time analysis.


  1. What is the alternative to DATE_PART date function in a COPY INTO TABLE command?

In Snowflake, the COPY INTO TABLE command does not natively support functions like DATE_PART directly within the command syntax. However, you can achieve similar results by using a staging table or a transformation step before performing the COPY INTO TABLE command. 

Radhika Sarraf
Content Specialist

Radhika is a Content Specialist at Hevo Data with over 5 years of experience. She excels at creating high-quality content related to data engineering, especially data integration, and data analytics. Her expertise lies in translating complex technical concepts into accessible and engaging content for diverse audiences.

All your customer data in one place.