Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

sysdate range to whole value

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:
Example

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

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');
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading