Convert timestamp to date in Oracle SQL

Question

How can we convert timestamp to date   The table has a field  start ts which is of the timestamp format    05 13 2016 4 58 11 123456 PM    I need to query the table and find the maximum and min timestamp in the table but I m not able to   Select max start ts   from db  where cast start ts as date     13-may-2016    But the query is not returning any values    Please help me in finding the max timestamp for a date

User · Accepted Answer

CAST timestamp expression AS DATE    For example  The query is   SELECT CAST SYSTIMESTAMP AS DATE  FROM dual

User · Answer

You can use  select to date to char date field  dd mm yyyy    from table

User · Answer

This may not be the correct way to do it  But I have solved the problem using substring function   Select max start ts   min start ts from db where SUBSTR start ts  0 9    13-may-2016    using this I was able to retrieve the max and min timestamp

User · Answer

If you have milliseconds in the date string  you can use the following  select TO TIMESTAMP SUBSTR  2020-09-10T09 37 28 378-07 00  1 23    YYYY-MM-DD quot T quot HH24 MI SS FF3  From Dual   And then convert it to Date with  select trunc TO TIMESTAMP SUBSTR  2020-09-10T09 37 28 378-07 00  1 23    YYYY-MM-DD quot T quot HH24 MI SS FF3    From Dual   It worked for me  hope it will help you as well

User · Answer

Try using TRUNC and TO DATE instead  WHERE     TRUNC start ts    TO DATE  2016-05-13    YYYY-MM-DD     Alternatively  you can use  gt   and  lt  instead to avoid use of function in the start ts column   WHERE    start ts  gt   TO DATE  2016-05-13    YYYY-MM-DD      AND start ts  lt  TO DATE  2016-05-14    YYYY-MM-DD

User · Answer

Format like this while selecting  to char systimestamp   DD-MON-YYYY    Eg  select to char systimestamp   DD-MON-YYYY   from dual

User · Answer

You can try the simple one select to date  2020-07-08T15 30 42Z   yyyy-mm-dd quot T quot hh24 mi ss quot Z quot    from dual

User · Answer

If the datatype is timestamp then the visible format is irrelevant   You should avoid converting the data to date or use of to char  Instead compare the timestamp data to timestamp values using TO TIMESTAMP    WHERE start ts  gt   TO TIMESTAMP  2016-05-13    YYYY-MM-DD      AND start ts  lt  TO TIMESTAMP  2016-05-14    YYYY-MM-DD

User · Answer

I d go with the following  Select max start ts   from db  where trunc start ts     date 13-may-2016

[sql] Convert timestamp to date in Oracle SQL

Examples related to sql

Examples related to oracle11g