The results differ more significantly if WEEK_START is set to any day other than Monday.
With WEEK_START set to 1, the DOW for Sunday is 7. With WEEK_START set to 0, the DOW for Sunday is 0.
#Aggregate date up to a date sql iso
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:Ġ, 1: The behavior is equivalent to the ISO week semantics, with the week starting on Monday.Ģ to 7: The “4 days” logic is preserved, but the first day of the week is different.ĪLTER SESSION SET WEEK_START = 0 SELECT d "Date", DAYNAME ( d ) "Day", DAYOFWEEK ( d ) "DOW", DATE_TRUNC ( 'week', d ) "Trunc Date", DAYNAME ( "Trunc Date" ) "Trunc Day", LAST_DAY ( d, 'week' ) "Last DOW Date", DAYNAME ( "Last DOW Date" ) "Last DOW Day", DATEDIFF ( 'week', '', d ) "Weeks Diff from to Date" FROM week_examples + -+-+-+-+-+-+-+-+ | Date | Day | DOW | Trunc Date | Trunc Day | Last DOW Date | Last DOW Day | Weeks Diff from to Date | |-+-+-+-+-+-+-+-| | | Fri | 5 | | Mon | | Sun | 0 | | | Sat | 6 | | Mon | | Sun | 0 | | | Sun | 0 | | Mon | | Sun | 0 | | | Mon | 1 | | Mon | | Sun | 1 | | | Tue | 2 | | Mon | | Sun | 1 | | | Wed | 3 | | Mon | | Sun | 1 | | | Thu | 4 | | Mon | | Sun | 1 | | | Sat | 6 | | Mon | | Sun | 52 | | | Sun | 0 | | Mon | | Sun | 52 | + -+-+-+-+-+-+-+-+ ALTER SESSION SET WEEK_START = 1 SELECT d "Date", DAYNAME ( d ) "Day", DAYOFWEEK ( d ) "DOW", DATE_TRUNC ( 'week', d ) "Trunc Date", DAYNAME ( "Trunc Date" ) "Trunc Day", LAST_DAY ( d, 'week' ) "Last DOW Date", DAYNAME ( "Last DOW Date" ) "Last DOW Day", DATEDIFF ( 'week', '', d ) "Weeks Diff from to Date" FROM week_examples + -+-+-+-+-+-+-+-+ | Date | Day | DOW | Trunc Date | Trunc Day | Last DOW Date | Last DOW Day | Weeks Diff from to Date | |-+-+-+-+-+-+-+-| | | Fri | 5 | | Mon | | Sun | 0 | | | Sat | 6 | | Mon | | Sun | 0 | | | Sun | 7 | | Mon | | Sun | 0 | | | Mon | 1 | | Mon | | Sun | 1 | | | Tue | 2 | | Mon | | Sun | 1 | | | Wed | 3 | | Mon | | Sun | 1 | | | Thu | 4 | | Mon | | Sun | 1 | | | Sat | 6 | | Mon | | Sun | 52 | | | Sun | 7 | | Mon | | Sun | 52 | + -+-+-+-+-+-+-+-+ This means that the first week and last week in the year may have fewer than 7 days. For this reason, both the YEAROFWEEK and YEAROFWEEKISOįunctions can provide the year that the week belongs to.ġ: January 1 always starts the first week of the year and December 31 is always in the last week of the year. Weeks have 7 days, but the first days of January and the last days of December might belong to a week in a different year. The parameter can have two values:Ġ: 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. The WEEK_OF_YEAR_POLICY session parameter controls how the WEEK and YEAROFWEEK functions behave. Ns, nsec, nanosec, nsecond, nanoseconds , Not controlled by the WEEK_START and WEEK_OF_YEAR_POLICY session parameters, as described in the next section. For usage details, see the next section, which describes how Snowflake handles calendar weeks and weekdays. Week_iso, weekofyeariso, weekofyear_iso The following two tables list the parts (case-insensitive) that can be used with these functions. Accepts all date and time parts (see next section for details).Īccepts relevant date parts (see next section for details).Īccepts relevant date and time parts (see next section for details).Īllows a time to be “rounded” to the start of an evenly-spaced interval.Ĭertain functions (as well as their appropriate aliases and alternatives) accept a date or time part as an argument.
0 kommentar(er)
