The date functions are most commonly used functions in the data warehouse. You can use date functions to manipulate the date expressions or variables containing date and time value. For example, get the current date, subtract date values, etc. In this article, we will check what are commonly used date functions in the Snowflake cloud data warehouse.
Many applications use date functions to manipulate the date and time data types. Each date value contains the century, year, month, day, hour, minute, second and milliseconds. Hence, date types are highly formatted and complicated data types. Almost all relational databases supports the date functions. The date functions in Snowflake are same or slightly different compared to other RDBMS.
Snowflake Date Functions
The date Functions are sub-divided into 7 types of functions.
- Current Date/Timestamp Functions
- Date/Timestamp Construction Functions
- Date/Time Part Extraction Functions
- Addition/Subtraction Date Functions
- Date/Time Truncation Functions
- Date Conversion Functions
- Timezone Conversion Functions
Now, let us check these functions in detail.
Current Date/Timestamp Functions in Snowflake
Following are the current date and timestamp functions.
| Function | Description |
| CURRENT_DATE | Current system date |
| CURRENT_TIME | Current system time |
| CURRENT_TIMESTAMP | Current system date with timestamp |
Date/Timestamp Construction Functions in Snowflake
Following are the date and time construction functions.
| Function | Description |
| DATE_FROM_PARTS | Creates a date from individual numeric components: year, month, day |
| TIME_FROM_PARTS | Creates a time from individual numeric components: hour, minutes, seconds, nanoseconds |
| TIMESTAMP_FROM_PARTS | Creates a timestamp from individual numeric components |
Date/Time Part Extraction Functions in Snowflake
Following are the date and time part extraction functions.
| Function | Description |
| DATE_PART | Extract specified date and time part. |
| EXTRACT | Extract specified date and time part. |
| DAYNAME | Extract 3 letter day of the week. |
| HOUR | Extract hour |
| MINUTE | Extract minutes |
| SECOND | Extract Seconds |
| LAST_DAY | Returns the last day of the specified date part for a date or timestamp. |
| MONTHNAME | Extract 3 letter month name from date/timestamp. |
| NEXT_DAY | Returns day of the week that comes after the input date. |
| PREVIOUS_DAY | Returns day of the week that comes before the input date. |
| YEAR/ YEAROFWEEK | Returns year |
| YEAROFWEEKISO | Same as YEAROFWEEK, except uses ISO semantics . |
| DAY/ DAYOFMONTH | Day of the month. |
| DAYOFWEEK | Day of the week. |
| DAYOFWEEKISO | The Day of the week uses ISO semantics . |
| DAYOFYEAR | Day of the year |
| WEEK/ WEEKOFYEAR | Week of the year |
| WEEKISO | Week of the year uses ISO semantics. |
| MONTH | Returns month |
| QUARTER | Returns quarter |
Related Articles,
Date Addition/Subtraction Functions in Snowflake
Following are the date addition/subtraction functions.
| Function | Description |
| ADD_MONTHS | Adds or subtracts a specified number of months to a date or timestamp. |
| DATEADD | Adds the specified value for the specified date or time part to a date, time, or timestamp. |
| DATEDIFF | Calculate difference between two dates and return date part. |
| TIMEADD | Adds the specified value for the specified date or time part to a date, time, or timestamp. |
| TIMEDIFF | Calculate difference between two time expressions and return date part. |
| TIMESTAMPADD | Adds the specified value for the specified date or time part to a date, time, or timestamp. |
| TIMESTAMPDIFF | Calculate difference between two timestamp expressions and return date part. |
Date/Time Truncation Functions in Snowflake
Following are the date/time truncate functions.
| Function | Description |
| DATE_TRUNC | Truncates a DATE, TIME, or TIMESTAMP to the specified precision. |
| TIME_SLICE | Calculates the beginning or end of a “slice” of time. |
| TRUNC | Truncates a date, time, or timestamp to the specified part. |
Date Conversion Functions in Snowflake
Following are date conversion functions.
| Function | Description |
| TO_DATE | Convert string containing date to date type. |
| TO_TIME | Convert string containing time to time type. |
| TO_TIMESTAMP | Convert string containing timestamp to timestamp type. |
| CAST | Convert string containing date to date or date time format. |
| :: | The cast operator is alternative to CAST function. |
Related Article,
Timezone Conversion Functions in Snowflake
Following function can be used to convert timezone.
| Function | Description |
| CONVERT_TIMEZONE | Converts a timestamp to another time zone. |
Snowflake Date Function Examples
Following are some of the date function usage examples.
- Get current date, time and timestamp value in Snowflake
For example,
select CURRENT_DATE;
+--------------+
| CURRENT_DATE |
|--------------|
| 2020-01-11 |
+--------------+
select CURRENT_TIMESTAMP;
+-------------------------------+
| CURRENT_TIMESTAMP |
|-------------------------------|
| 2020-01-11 10:22:06.433 -0800 |
+-------------------------------+
select CURRENT_TIME;
+--------------+
| CURRENT_TIME |
|--------------|
| 10:22:25 |
+--------------+- Subtract month to current date and convert it to YYYYMMDD format in Snowflake
For example,
select to_char(add_months(current_date, -1), 'YYYYMMDD') as int;
+----------+
| YYYYMMDD |
|----------|
| 20191211 |
+----------+- Add month to current date and convert it to integer format in Snowflake
For example,
select to_number(to_char(add_months(current_date, 1),'YYYYMMDD'),'99999999') as INT;
+----------+
| INT |
|----------|
| 20200211 |
+----------+- Extract year,day,month part from the current date in Snowflake
select extract (year from current_date) as yr;
+------+
| YR |
|------|
| 2020 |
+------+
select date_trunc('year', current_date) as yr;
+------------+
| YR |
|------------|
| 2020-01-01 |
+------------+
select extract (day from current_date) as day;
+-----+
| DAY |
|-----|
| 11 |
+-----+
select extract (month from current_date) as mon;
+-----+
| MON |
|-----|
| 1 |
+-----+- Construct Date from Individual Numeric Parts
For example,
select DATE_FROM_PARTS(2020, 1, 20) as dt;
+------------+
| DT |
|------------|
| 2020-01-20 |
+------------+- Get first and last day of previous month in Snowflake
For examples,
select last_day(current_date - interval '2 month') + interval '1 day' as first_day;
+------------+
| FIRST_DAY |
|------------|
| 2019-12-01 |
+------------+
select last_day( current_date - interval '1 month' ) as last_day;
+------------+
| LAST_DAY |
|------------|
| 2019-12-31 |
+------------+Related Articles,
- Snowflake Interval Data Types and Conversion Examples
- Snowflake Concat Function and Operator – Examples
Hope this helps 🙂