sysdate range to whole value-CodePudding

Is it possible to turn the current time stamp to a whole number? Example: If sysdate returns 1/19/2022 5:36:49 PM can I turn that to 1/19/2022 5PM since it falls in the 5PM range.

Here is my query

    Select FACILITY, TRK_ID,  LOT_DTTM, IN_QTY
from TRK_ID_LOT
WHERE facility in 'DP1DM5'
and trk_id like ('AE%')
and lot_dttm > sysdate - 1

EXAMPLE:

CodePudding user response：

Truncate it to hours:

SQL> select trunc(to_date('1/19/2022 5:36:49 PM', 'mm/dd/yyyy hh:mi:ss pm'), 'hh') res
  2  from dual;

RES
----------------------
01/19/2022 05:00:00 PM

SQL>

If you want to update rows, do so using the same function:

update your_table set
  date_column = trunc(date_column, 'hh');