For example, Get the first day of the current month: Get the last day of the current month as a DATE value using the DATEADD and DATE_TRUNC functions: Alternative option. Feel free to explore this service with a free 14-day trial today! The date functions in Snowflake are same or slightly different compared to other RDBMS. Compute can be scaled up, down, out, or in and there is no effect on storage used. ADD_MONTHS function Usage. Virtual Warehouses have no effect on compute costs. 0 to 7. Please select platform and region to see pricing. The latest-ever first snow in Denver fell on Nov. 21, 1934, so we're still 11 days out from breaking that record. Turn compute resources on and off, so you only pay for what you use, Take advantage of cost-effective compression in Snowflake to store near unlimited amounts of data, Grow your analytics infrastructure with linear cost scalability. Now we can check for first day of month , Select trunc((sysdate),'month') as First_day_of_month from dual; First_day_of_month. -- Output short English names, e.g. Asking for help, clarification, or responding to other answers. Truncates a DATE, TIME, or TIMESTAMP to the specified precision. How do you ensure that a red herring doesn't violate Chekhov's gun? Truncates a DATE, TIME, or TIMESTAMP to the specified precision. Site design / logo 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. March opens with a average high of 46 and warms to 58 on the 31st. rounded to the nearest integer. For this reason, both the YEAROFWEEK and YEAROFWEEKISO fractional seconds) that were truncated: ------------+-------------------+--------------------+------------------+, | DATE1 | TRUNCATED TO YEAR | TRUNCATED TO MONTH | TRUNCATED TO DAY |, |------------+-------------------+--------------------+------------------|, | 2015-05-08 | 2015-01-01 | 2015-05-01 | 2015-05-08 |, -------------------------+-------------------------+-------------------------+-------------------------+, | TIMESTAMP1 | TRUNCATED TO HOUR | TRUNCATED TO MINUTE | TRUNCATED TO SECOND |, |-------------------------+-------------------------+-------------------------+-------------------------|, | 2015-05-08 23:39:20.123 | 2015-05-08 23:00:00.000 | 2015-05-08 23:39:00.000 | 2015-05-08 23:39:20.000 |. I wonder if someone can help me find a good command to achieve this. Then, the date is changed to the first day of the month by using the DATE_FROM_PARTS function. 3. Making statements based on opinion; back them up with references or personal experience. 1. 1 to 31. If the input is a TIMESTAMP_NTZ, then the The date Functions are sub-divided into 7 types of functions. Date and time calculations are among the most widely used and most critical computations in analytics and data mining. through 7 for Sunday. Returning the first day of a month in MySQL. What is the point of Thrower's Bandolier? It can be 1st day of the month at 12 AM. An important aspect of understanding how these Adds or subtracts a specified number of months to a date or timestamp. By clicking Accept, you are agreeing to our cookie policy. To retrieve the date for the first Tuesday, Wednesday, etc., substitute 2, 3, and so on, respectively, through 7 for Sunday. last_day : The last day is nothing but last day of the month. Allows a time to be rounded to the start of an evenly-spaced interval. monthstart - script and chart function This function returns a value corresponding to a timestamp of the first millisecond of the first day of the month containing date. determine the last day of the current month. Final Thoughts Calculated using weeks starting on Monday. Note that the function results differ depending on how the parameter is set: Param set to 0 (default / legacy behavior). It is always 01-01. output is a TIMESTAMP_NTZ. YOW for Jan 2nd and 3rd, 2017 moves to 2016 (from 2017). I am not able to find any good conversion code to get 2014-02-17 into 2014-02-01 without having to use concatenation and a ton of formatting. Almost all relational databases supports the date functions. Find centralized, trusted content and collaborate around the technologies you use most. The session parameter WEEK_OF_YEAR_POLICY is set to indicate that the first week How to follow the signal when reading the schematic? The session parameter WEEK_OF_YEAR_POLICY is set to indicate that the first week [1] For usage details, see the next section, which describes how Snowflake handles calendar weeks and weekdays. hour, minute, second), the function returns a TIMESTAMP_NTZ value, with 00:00:00.000 as the starting time for the date. Snowflake provides a special set of week-related date functions (and equivalent data parts) whose behavior is consistent with the ISO week semantics: Extracting the quarter date part from a timestamp returns the quarter number of the year in the timestamp. 1 to 54. Which of the following statements about Snowflake are true? Commonly used to return the last day of the month for a date or timestamp. Table : orders . ALTER SESSION SET WEEK_OF_YEAR_POLICY = 1; SELECT '2016-01-02T23:39:20.123-07:00 . MONTH. Month of the average first accumulating (0.1 inch or greater) snowfall of the season, according to 30-year average statistics. "Sun", "Mon" etc. The above query will give the first day of the month in oracle. Each date value contains the century, year, month, day, hour, minute, second and milliseconds. -- Output arbitrary, explicitly-provided month names, ----------------------------------------+, | DATE_TRUNC('DAY', CURRENT_TIMESTAMP()) |, |----------------------------------------|, | Wed, 07 Sep 2016 00:00:00 -0700 |, -- Comparison with explicit casting to date, Date and Time Formats in Conversion Functions, --------------------------------------------------------------------------------+, | DATEADD('DAY',5,TO_TIMESTAMP ('12-JAN-2016 00:00:00','DD-MON-YYYY HH:MI:SS') ) |, |--------------------------------------------------------------------------------|, | Thu, 17 Jan 2016 00:00:00 -0800 |, -------------------------------------------------------------------------------------------------+, | DATEDIFF('DAY', TO_TIMESTAMP ('12-JAN-2016 00:00:00','DD-MON-YYYY HH:MI:SS') , CURRENT_DATE() ) |, |-------------------------------------------------------------------------------------------------|, | 240 |, -------------------------------------------------------------------------------------------+, | DATEDIFF('DAY', TO_DATE ('12-JAN-2016 00:00:00','DD-MON-YYYY HH:MI:SS'), CURRENT_DATE() ) |, |-------------------------------------------------------------------------------------------|, | 240 |, -----+------------+-----------------+-----------+---------+, | N | THEDATE | THEDAYOFTHEWEEK | THEMONTH | THEYEAR |, |-----+------------+-----------------+-----------+---------|, | 1 | 2016-01-01 | Friday | January | 2016 |, | 2 | 2016-01-02 | Saturday | January | 2016 |, | 364 | 2016-12-29 | Thursday | December | 2016 |, | 365 | 2016-12-30 | Friday | December | 2016 |, Inserting Valid Date Strings Into Date Columns in a Table, Calculating Business Calendar Dates and Times, Converting Valid Character Strings to Dates, Times, or Timestamps, Calculating Differences Between Dates or Times. This works by inputting 01 in the day part of the function. For example, setting the parameter to 3 (Wednesday) changes the results of all the week-related functions Expand Post. 37 degrees with a breezy easterly wind. I would like to schedule my snowflake task to run once a month. Our expert services and consulting teams can help you implement Snowflake as efficiently as possible, and integrate your existing analytics systems with our Data Cloud. ADD_MONTHS function in Snowflake - Syntax and Examples. Same as DAYOFWEEK, except uses ISO semantics. I suppose I should use cron statements. Note that this behavior is also influenced by the start day of the week, as controlled by the value set for the WEEK_START session parameter: 0 , 1: The behavior is equivalent to the ISO week semantics, with the week starting on Monday. Setting WEEK_START to 0 (legacy behavior) or 1 (Monday) does not have a significant effect, as illustrated in the following two examples: With WEEK_START set to 0, the DOW for Sunday is 0. treated as a TIMESTAMP with hours, minutes, seconds, and fractions of The returned value is the same type as the input value. Learn firsthand how Snowflake delivers whats possible with the Data Cloud. For more details, including examples, see Hence, date types are highly formatted and complicated data types. Its fast, free and secure. You can set the parameter WEEK_START (https://docs.snowflake.net/manuals/sql-reference/parameters.html#label-week-start) to any day of the week you choose. I'm trying to derive a date field that represents the first business day (in this case, it means the first weekday - also excluding holidays) of the next month. ++ (columns 3 through 8): The following example sets both parameters to 0 to follow ISO-like semantics (i.e. give us February 31. Schema : TPCH_SF10. Syntax LAST_DAY( <date_or_time_expr> [ , <date_part> ] ) Usage Notes date_or_time_expr ( Required) must be a date or timestamp expression. Get the first day of the current year as a DATE value: SELECT DATE_TRUNC('year', current_date()); Get the last day of the current year as a DATE value: -------------------------+------+-----------------+-------+-----+--------------+-------------+, | TSTAMP | YEAR | QUARTER OF YEAR | MONTH | DAY | DAY OF MONTH | DAY OF YEAR |, |-------------------------+------+-----------------+-------+-----+--------------+-------------|, | 2013-05-08 23:39:20.123 | 2013 | 2 | 5 | 8 | 8 | 128 |, -------------------------+------+----------+--------------+--------------+------------------+, | TSTAMP | WEEK | WEEK ISO | WEEK OF YEAR | YEAR OF WEEK | YEAR OF WEEK ISO |, |-------------------------+------+----------+--------------+--------------+------------------|, | 2016-01-02 23:39:20.123 | 1 | 53 | 1 | 2016 | 2015 |, | 2016-01-02 23:39:20.123 | 53 | 53 | 53 | 2015 | 2015 |, -------------------------+-------------+-----------------+, | TSTAMP | DAY OF WEEK | DAY OF WEEK ISO |, |-------------------------+-------------+-----------------|, | 2016-01-02 23:39:20.123 | 7 | 6 |, | 2016-01-02 23:39:20.123 | 6 | 6 |. 2. TOLEDO, Ohio (WTVG) - Heavy rain and thunderstorms are likely starting around mid-day. DAYOFWEEKISO , WEEKISO , YEAROFWEEKISO. Calculate difference between two timestamp expressions and return date part. This topic provides practical examples of common date and time queries These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part (see Supported Date and Time Parts). Truncates a date, time, or timestamp to the specified part. With WEEK_START set to 1, the DOW for Sunday is 7. This is the number of units of time that you want to add. Note that truncation is not the same as extraction. The TO_DATE function accepts TIMESTAMP values and even strings in TIMESTAMP format, but discards the time information If date_or_time_part is day or larger (e.g. When you retrieve DATE information, you can format it as a TIMESTAMP if you wish: Get the current date and time as a TIMESTAMP value: Get the current day of the week as a number using the EXTRACT function: The dayofweek_iso part follows the ISO-8601 data elements and interchange formats standard. This means that all the This is the date or timestamp expression to which you want to add The session parameter WEEK_OF_YEAR_POLICY is set to indicate that the first week of the year is the week that contains January 1st of that year. Does ZnSO4 + H2 at high pressure reverses to Zn + H2SO4? execute this query and check if you are looking for data something like this. In the following example, the TIMESTAMP_TYPE_MAPPING parameter is set to TIMESTAMP_LTZ (local time zone). Returns day of the week that comes after the input date. | 2019-12-01 | What am I doing wrong here in the PlotLegends specification? the week December 26, 2010 through January 1st, 2011 is considered the last week of 2010, not the first week 1 Answer. How do I get current date/time on the Windows command line in a suitable format for usage in a file/folder name? LAST_DAY function in Snowflake - SQL Syntax and Examples LAST_DAY Description Returns the last day of the specified date part for a date or timestamp. 01-08-2020. Convert string containing date to date or date time format. Calendar Weeks and Weekdays. Most week-related functions are controlled only by the WEEK_START session parameter. But the one before that, 2021, still stands at number 2 for snowiest Februarys on record with. For example, if today is January 31, adding a month should not Truncates a DATE, TIME, or TIMESTAMP to the specified precision. the day belongs to the last week in the previous year). AZFamily's First Alert Weather App First lets you track storms and get severe weather alerts whereever you are. Commonly used to return the last day of the month for a date or timestamp. This family of functions can be used to construct, convert, extract, or modify DATE/TIME/TIMESTAMP data. Each RDBMS may employ different date functions, and there may also be differences in the syntax for each RDBMS even when the function call is the same. Now, let us check these functions in detail. No one knows Snowflake better. Not the answer you're looking for? [1] Results dictated by the values set for the WEEK_OF_YEAR_POLICY and/or WEEK_START session parameters. Start for free Certain functions (as well as their appropriate aliases and alternatives) accept a date or time part as an argument. How to Optimize Query Performance on Redshift? and calculations. Convert string containing time to time type. Thanks! ADD_MONTHS returns slightly different results than DATEADD used with a MONTH component:. For both ADD_MONTHS and , if the result month has fewer days than the original day, the result day of the month is the last day of the result month. If you insert a DATE that was defined with only a time, then the default date is January 1, 1970. The TIMEZONE parameter is set to America/Chicago Convert string containing timestamp to timestamp type. Almost all relational databases supports the date functions. week starts on Monday and all weeks have 7 days): The next example illustrates the effect of keeping WEEK_OF_YEAR_POLICY set to 0, but changing WEEK_START to 3 (Wednesday): WOY for Jan 1st, 2017 moves to week 53 (from 52). ADD_MONTHS returns slightly different results than DATEADD used with a MONTH component: For both ADD_MONTHS and DATEADD, if the result month has fewer days than the original day, the result day of the month is the last day of the result month. Copyright 2023 Datameer, Inc. All rights reserved. Snowflake Date Conversion Functions and Examples, Snowflake Interval Data Types and Conversion Examples, Snowflake Concat Function and Operator Examples. The parameter can have two values: 0: The affected week-related functions use semantics similar to the ISO semantics, in which a week belongs to a given year if at least 4 days of that week are in that year. a second all initially set to 0 (e.g. ------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+, | Date | Day | DOW | Trunc Date | Trunc Day | Last DOW Date | Last DOW Day | Weeks Diff from 2017-01-01 to Date |, |------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------|, | 2016-12-30 | Fri | 5 | 2016-12-26 | Mon | 2017-01-01 | Sun | 0 |, | 2016-12-31 | Sat | 6 | 2016-12-26 | Mon | 2017-01-01 | Sun | 0 |, | 2017-01-01 | Sun | 0 | 2016-12-26 | Mon | 2017-01-01 | Sun | 0 |, | 2017-01-02 | Mon | 1 | 2017-01-02 | Mon | 2017-01-08 | Sun | 1 |, | 2017-01-03 | Tue | 2 | 2017-01-02 | Mon | 2017-01-08 | Sun | 1 |, | 2017-01-04 | Wed | 3 | 2017-01-02 | Mon | 2017-01-08 | Sun | 1 |, | 2017-01-05 | Thu | 4 | 2017-01-02 | Mon | 2017-01-08 | Sun | 1 |, | 2017-12-30 | Sat | 6 | 2017-12-25 | Mon | 2017-12-31 | Sun | 52 |, | 2017-12-31 | Sun | 0 | 2017-12-25 | Mon | 2017-12-31 | Sun | 52 |, | 2017-01-01 | Sun | 7 | 2016-12-26 | Mon | 2017-01-01 | Sun | 0 |, | 2017-12-31 | Sun | 7 | 2017-12-25 | Mon | 2017-12-31 | Sun | 52 |, | 2016-12-30 | Fri | 3 | 2016-12-28 | Wed | 2017-01-03 | Tue | 0 |, | 2016-12-31 | Sat | 4 | 2016-12-28 | Wed | 2017-01-03 | Tue | 0 |, | 2017-01-01 | Sun | 5 | 2016-12-28 | Wed | 2017-01-03 | Tue | 0 |, | 2017-01-02 | Mon | 6 | 2016-12-28 | Wed | 2017-01-03 | Tue | 0 |, | 2017-01-03 | Tue | 7 | 2016-12-28 | Wed | 2017-01-03 | Tue | 0 |, | 2017-01-04 | Wed | 1 | 2017-01-04 | Wed | 2017-01-10 | Tue | 1 |, | 2017-01-05 | Thu | 2 | 2017-01-04 | Wed | 2017-01-10 | Tue | 1 |, | 2017-12-30 | Sat | 4 | 2017-12-27 | Wed | 2018-01-02 | Tue | 52 |, | 2017-12-31 | Sun | 5 | 2017-12-27 | Wed | 2018-01-02 | Tue | 52 |, ------------+-----+-----+-----------+------+-----------+, | Date | Day | WOY | WOY (ISO) | YOW | YOW (ISO) |, |------------+-----+-----+-----------+------+-----------|, | 2016-12-30 | Fri | 52 | 52 | 2016 | 2016 |, | 2016-12-31 | Sat | 52 | 52 | 2016 | 2016 |, | 2017-01-01 | Sun | 52 | 52 | 2016 | 2016 |, | 2017-01-02 | Mon | 1 | 1 | 2017 | 2017 |, | 2017-01-03 | Tue | 1 | 1 | 2017 | 2017 |, | 2017-01-04 | Wed | 1 | 1 | 2017 | 2017 |, | 2017-01-05 | Thu | 1 | 1 | 2017 | 2017 |, | 2017-12-30 | Sat | 52 | 52 | 2017 | 2017 |, | 2017-12-31 | Sun | 52 | 52 | 2017 | 2017 |, | 2016-12-30 | Fri | 53 | 52 | 2016 | 2016 |, | 2016-12-31 | Sat | 53 | 52 | 2016 | 2016 |, | 2017-01-01 | Sun | 53 | 52 | 2016 | 2016 |, | 2017-01-02 | Mon | 53 | 1 | 2016 | 2017 |, | 2017-01-03 | Tue | 53 | 1 | 2016 | 2017 |, | 2017-01-01 | Sun | 1 | 52 | 2017 | 2016 |, | 2017-01-02 | Mon | 2 | 1 | 2017 | 2017 |, | 2017-01-03 | Tue | 2 | 1 | 2017 | 2017 |, | 2017-01-04 | Wed | 2 | 1 | 2017 | 2017 |, | 2017-01-05 | Thu | 2 | 1 | 2017 | 2017 |, | 2017-12-30 | Sat | 53 | 52 | 2017 | 2017 |, | 2017-12-31 | Sun | 53 | 52 | 2017 | 2017 |. Accepts relevant date parts (see next section for details). Adds the specified value for the specified date or time part to a date, time, or timestamp. Redshift RSQL Control Statements IF-ELSE-GOTO-LABEL. How do you get out of a corner when plotting yourself into a corner. Therefore changing the date to the first day of the month, without writing any code. Simplifying Date Transformation & Modeling With Datameer. Extracts the corresponding date part from a date or timestamp. I tried to do it like this but I am still getting an error SCHEDULE = ' Using Cron 0 0 1 * * /usr/bin/foo' I Have Also tried this SCHEDULE = ' Using Cron 10 0 1 * * America/Los_Angeles' ++, As you can tell after assessing the four options, feel free to decide for yourself which is most convenient. There is still quite a bit of uncertainty in the . the day belongs to the first week in the next year). Following are the date and time part extraction functions. This means that the first week and last week in the year may have fewer than 7 days. Thursday won't be quite as warm as winds turn to the northeast and clouds increase ahead of the next area of low pressure that'll arrive on Friday. If the value is a The session parameter WEEK_START is set to indicate that the week starts on Sunday. For the end of the workweek, we are tracking a system that could bring some moderate snow, and some rain along the . This unit of measure must Add a month to a date in a month with the same or more days than the resulting month. Converts a timestamp to another time zone. The timestamps are loaded in UTC time and converted to other time zones: This example show how to insert a value into a DATE column. SELECT DATE_TRUNC (DATE'2021-01-15', MONTH) -- '2021-01-01' (Returns the First day of month) Try our Free Online Converter for Snowflake Roboquery converts this function and lot of other unsupported datatypes, functions, statements & operators in just a click. be different from the original day. integer. Can I tell police to wait and call a lawyer when served with a search warrant? For more details, including examples, see This indicates the units of time that you want to add. Like Liked Unlike Reply. You can use date functions to manipulate the date expressions or variables containing date and time value. DATE_TRUNC() Snowflake Official Documentation Link. The function returns the day of the week as an integer value in the range 0-6, where 0 represents Sunday. The following demonstrates the use of the functions DAYOFWEEK and DAYOFWEEKISO. I am new to snowflake and my manager wants me to retrieve the data of the past month when it is 5th of the current month. Convert string containing date to date type. SELECT DATEADD(MONTH, 1, '2000-01-31'::DATE) AS DIFFERENT_DAY; Output: The following demonstrates the use of the functions YEAR, MONTH, DAY, DAYOFWEEK, DAYOFYEAR, date_or_time_expr can be a date, time, or timestamp. The last two examples set WEEK_OF_YEAR_POLICY to 1 and set WEEK_START first to 1 (Monday) and then 3 (Wednesday): With WEEK_OF_YEAR_POLICY set to 1 and WEEK_START set to 1 (Monday): Note that this is the most common usage scenario, based on feedback weve received. ADD_MONTHS returns slightly different results than used with a MONTH component:. Same as YEAROFWEEK, except uses ISO semantics . The results differ more significantly if WEEK_START is set to any day other than Monday. How do I get the current date and time in PHP? 1 to 7. Snowflake Date Functions resulting month. How to find the duplicate data in a table in SQL? For example, if you want to add 2 days to August 1, 2018, then this will The cast operator is alternative to CAST function. date_or_time_part must be one of the values listed in Supported Date and Time Parts. The data type of the returned value is the same as the data type of the Give it a try now Convert to Snowflake [2] Not controlled by the WEEK_START and WEEK_OF_YEAR_POLICY session parameters, as described in the next section. Add 2 months to a date and cast the date to a timestamp with no time zone: Demonstrate preservation of end-of-month information: Add one month to the last day of February 2016 (a leap year). You can use following query also to find out first day of the month, Returning the first day of a month in SQL SERVER, 3.) Each date value contains the century, year, month, day, hour, minute, and second. Suppose a set of incoming timestamps has a different time zone specified. If a set of incoming timestamps has no time zone specified, then Snowflake loads those strings assuming the timestamps represent local time in the set time zone. Calculates the beginning or end of a slice of time. We received 28.3 . WOY for Jan 2nd and 3rd, 2017 moves to week 53 (from 1). 1 to 53. Hence, date types are highly formatted and complicated data types. For example, get the current date, subtract date values, etc. Day (number) of the year. Date Part Extracted from Input Date / Timestamp, Same as YEAROFWEEK, except uses ISO semantics, Same as DAYOFWEEK, except uses ISO semantics. DAY , DAYOFMONTH. Or, secure discounts to Snowflakes usage-based pricing by buying pre-purchased Snowflake capacity options. If so, try this DATEADD( month, -1 , date_trunc('month', current_date()) ) Expand Post. Month . time. Following are the date and time construction functions. This is the number of months you want to add. select ct.O_CUSTKEY,dateadd('day',1,last_day(ct.O_ORDERDATE)) start_date,to_char(ct.O_ORDERDATE . Take out your foil paper, construction paper, glitters, scissors, and design templates (if you have any), and then just cut out basic shapes or intricate patterns into a beautiful snowflake. The date functions are most commonly used functions in the data warehouse. Following are the current date and timestamp functions. For details about ISO semantics and the parameter, see Calendar Weeks and Weekdays. Return the last day of the month for the specified date (from a timestamp): Return the last day of the year for the specified date (from a timestamp). ++ WEEKISO. It may be positive or negative. LAST_DAY function Syntax LAST_DAY( <date_or_time_expr> [ , <date_part> ] ) LAST_DAY function Examples The thunderstorms will mix with snow and . "Jan", "Dec", etc. If date_or_time_part is smaller than a day (e.g. When date_or_time_part is year, quarter, or month (or any of their variations), if the result month has fewer days than the original day of the month, the result day of the month might want to add 2 days, then this will be DAY. ns , nsec , nanosec , nsecond , nanoseconds , midnight on the specified date). We actually had even less snow last February, with only 2.6" inches for the month. Compute usage is billed on a per-second basis, with a minimum of 60 seconds. date_or_time_expr (Required) must be a date or timestamp expression. Note that in all cases, the returned value is a TIMESTAMP, Based on feedback weve received, the most common scenario is to set both parameters to 1. ), see Calendar Weeks and Weekdays. YEAROFWEEKISO. GMB's answer is the most appropiate one, I am also sharing the entire SQL that you can use select date_trunc('MONTH', to_date('2014-02-17')) as FIRST_DAY_MONTH; How to truncate a date to the first day of the month in Snowflake? Returns the last day of the specified date part for a date or timestamp. vegan) just to try it, does this inconvenience the caterers and staff? The function returns the day of the week as an integer value in the range 1-7, where 1 represents Monday. influence the results of the functions. For example: Truncating a timestamp down to the quarter returns the timestamp corresponding to midnight of the first day of the quarter for the input timestamp. 2.) No one knows Snowflake better. Datameer supports advanced date operations such as : To find the first day, the date time is first converted to date only using TO_DATE function. Following are the date/time truncate functions. Connect to SQL Server From Spark PySpark, Rows Affected by Last Snowflake SQL Query Example, Snowflake Scripting Cursor Syntax and Examples, DBT Export Snowflake Table to S3 Bucket, Snowflake Scripting Control Structures IF, WHILE, FOR, REPEAT, LOOP, Creates a date from individual numeric components: year, month, day, Creates a time from individual numeric components: hour, minutes, seconds, nanoseconds, Creates a timestamp from individual numeric components. For both ADD_MONTHS and DATEADD, if the result month has fewer days than the original day, the result day of the month is the last day of the result month.. For ADD_MONTHS only, if the original day is the last day of the month, the result day of month will be the last day of the result month. date_part (Optional) is the date part for which the last day is returned. num_months_expr can be a positive or negative integer to either add or subtract months, respectively. Snowflake supports date_trunc() for datatypes DATE, TIME, and TIMESTAMP: SELECT TRUNCTIMESTAMPTOMONTH(CURRENT_DATE()); Sounds like you're working with strings. Truncate a date (from a timestamp) down to the year, month, and day: Truncate a TIMESTAMP down to the hour, minute, and second. Day (number) of the month. whereas in Snowflake: select DATEADD( month, -1 , date_trunc('month', current_date()) ) --o/p : 2019-05-01. For more detailed examples of the week-related functions (DAYOFWEEK, WEEK, WEEKOFYEAR, YEAROFWEEK, etc.