[Wranglers] Rounding datetime values in Oracle

[Ali, Saqib]

Oracle's ROUND(<dt>, <fmt>) function returns a date rounded to the period
specified as a parameter in the form of a date format. For example, if you
specify "YEAR" as the parameter, the function will round the date either to
the first day of the year (if the date is before July) or to the last day
of the year (if the date is in July or later).

The rounding is applied at:
Year - 1st July
Quarter - 16th of second month in quarter
Month - 16th of month
Week - 3.5 days into week

Check out the examples!


"CURRENT_DATE"                "ROUNDED_VALUE"
"ROUNDING_GRAIN"              "ROUNDING_DESCRIPTION"
"03/07/25"                    "30/12/24"                    "IYYYY"
              "Year containing the calendar week, as defined by the ISO
8601 standard"
"03/07/25"                    "01/01/25"                    "SYYYY"
              "Year (rounds up on July 1)"
"03/07/25"                    "29/06/25"                    "DAY"
              "Starting day of the week"
"03/07/25"                    "30/06/25"                    "IW"
               "Same day of the week as the first day of the calendar week
as defined by the ISO 8601 standard, which is Monday"
"03/07/25"                    "01/07/25"                    "MM"
               "Month (rounds up on the sixteenth day)"
"03/07/25"                    "01/07/25"                    "Q"
              "Quarter (rounds up on the sixteenth day of the second month
of the quarter)"
"03/07/25"                    "01/07/25"                    "W"
              "Same day of the week as the first day of the month"
"03/07/25"                    "02/07/25"                    "WW"
               "Same day of the week as the first day of the year"
"03/07/25"                    "03/07/25"                    "DD"
               "Day"



select CURRENT_DATE, TRUNC(CURRENT_DATE, 'W') as rounded_value, 'W' as
rounding_grain, 'Same day of the week as the first day of the month' as
rounding_description
union
select CURRENT_DATE, TRUNC(CURRENT_DATE, 'SYYYY'), 'SYYYY', 'Year (rounds
up on July 1)'
union
select CURRENT_DATE, TRUNC(CURRENT_DATE, 'IYYY'), 'IYYYY', 'Year containing
the calendar week, as defined by the ISO 8601 standard'
union
select CURRENT_DATE, TRUNC(CURRENT_DATE, 'Q'), 'Q', 'Quarter (rounds up on
the sixteenth day of the second month of the quarter)'
union
select CURRENT_DATE, TRUNC(CURRENT_DATE, 'MM'), 'MM', 'Month (rounds up on
the sixteenth day)'
union
select CURRENT_DATE, TRUNC(CURRENT_DATE, 'WW'), 'WW', 'Same day of the week
as the first day of the year'
union
select CURRENT_DATE, TRUNC(CURRENT_DATE, 'IW'), 'IW', 'Same day of the week
as the first day of the calendar week as defined by the ISO 8601 standard,
which is Monday'
union
select CURRENT_DATE, TRUNC(CURRENT_DATE, 'DD'), 'DD', 'Day'
union
select CURRENT_DATE, TRUNC(CURRENT_DATE, 'DAY'), 'DAY', 'Starting day of
the week'
;
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://analyticsengineering.net/pipermail/wranglers/attachments/20250703/c9831d55/attachment.htm>