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.

Solution 1

CAST(timestamp_expression AS DATE)

For example, The query is : SELECT CAST(SYSTIMESTAMP AS DATE) FROM dual;

Solution 2

Try using TRUNC and TO_DATE instead

WHERE
    TRUNC(start_ts) = TO_DATE('2016-05-13', 'YYYY-MM-DD')

Alternatively, you can use >= and < instead to avoid use of function in the start_ts column:

WHERE
   start_ts >= TO_DATE('2016-05-13', 'YYYY-MM-DD')
   AND start_ts < TO_DATE('2016-05-14', 'YYYY-MM-DD')

Solution 3

Format like this while selecting:

to_char(systimestamp, 'DD-MON-YYYY')

Eg:

select to_char(systimestamp, 'DD-MON-YYYY') from dual;

Solution 4

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 >= TO_TIMESTAMP('2016-05-13', 'YYYY-MM-DD')
   AND start_ts < TO_TIMESTAMP('2016-05-14', 'YYYY-MM-DD')

Solution 5

You can try the simple one

select to_date('2020-07-08T15:30:42Z','yyyy-mm-dd"T"hh24:mi:ss"Z"') from dual;

View more solutions

Comments

[Dinu]

I have tried using this but since the timestamp contains AM / PM with milliseconds, it not returning any values.

[Dinu]

if the table contains data for multiple dates and the table is not having any date field. The date is identified using the timestamp field. then how can we find the max and min timestamp for a particular date??

[Paul Maxwell]

MIN and MAX still work on timestamps. This datatype is just a very accurate way of storing dates and times

[Dinu]

Yes I agree.. We can find the max timestamp for the day but how can we find the min timestamp for the latest date. If we user Select min(start_ts) from db, then it will return the minimum timestamp present in the table. It wont be the minimum timestamp for the latest date. Since date column is missing how can we find the min timestamp for the latest date.??

[Paul Maxwell]

You have selected an answer that doesn't solve this recent comment. In effect you have a new question.

[Dinu]

That was selected by mistake.. Should I post a new question... Or do you know the answer.. It would be great if you could reply...

[Paul Maxwell]

I suspect you should just open a new question but this time provide "sample data" and the "expected result" because doing that helps you to explain what it you really need. It also helps us because we get to see exactly what it is that you need without needing lots of words.

[Paul Maxwell]

Converting data to compare to a constant (e.g. cast(start_ts as date) = '13-may-2016') is very poor for performace as it affects access to indexes on the column you just converted, unless you build a function based index and use that same function in the query. Research sargable.

[Tarun Kumar]

to_char() function is useful when you want to convert TIMESTAMP to string date representation.

[Tarun Kumar]

Yes, above make sense when you have timestamp as string. If you are directly reading the timestamp column then below makes sense. to_char(systimestamp, 'DD-MON-YYYY')