50

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Dinu
  • 845
  • 3
  • 13
  • 27
  • See one of the many other questions about comparing a date to a timestamp, such as this one: http://stackoverflow.com/questions/30519823/compare-date-in-sql-command – Turophile Jun 01 '16 at 05:02
  • _real_ timestamp columns do not have "a format". Why are you storing that as a string? –  Jun 01 '16 at 07:56
  • For those who came here wanting to convert a timestamp rather than a formatted date string: See this question on how to [convert a real timestamp (just a number, not formatted for a specific time zone) to a value of type DATE](https://dba.stackexchange.com/a/16466). – c0xc Aug 30 '18 at 11:48

9 Answers9

84
CAST(timestamp_expression AS DATE)

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

Bhanuchander Udhayakumar
  • 1,581
  • 1
  • 12
  • 30
Peter Nosko
  • 1,078
  • 1
  • 9
  • 10
  • 2
    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`. – Paul Maxwell Oct 02 '18 at 05:59
25

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')
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
  • I have tried using this but since the timestamp contains AM / PM with milliseconds, it not returning any values. – Dinu Jun 01 '16 at 07:30
14

Format like this while selecting:

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

Eg:

select to_char(systimestamp, 'DD-MON-YYYY') from dual;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
theDbGuy
  • 903
  • 1
  • 9
  • 22
6

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')
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • 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?? – Dinu Jun 03 '16 at 10:42
  • MIN and MAX still work on timestamps. This datatype is just a very accurate way of storing dates and times – Paul Maxwell Jun 03 '16 at 11:45
  • 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.?? – Dinu Jun 07 '16 at 04:30
  • You have selected an answer that doesn't solve this recent comment. In effect you have a new question. – Paul Maxwell Jun 07 '16 at 09:49
  • 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... – Dinu Jun 07 '16 at 10:31
  • 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 Jun 07 '16 at 12:23
3

You can try the simple one

select to_date('2020-07-08T15:30:42Z','yyyy-mm-dd"T"hh24:mi:ss"Z"') from dual;
HK boy
  • 1,398
  • 11
  • 17
  • 25
A1991
  • 47
  • 1
  • 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') – Tarun Kumar Sep 01 '20 at 12:35
0

You can use:

select to_date(to_char(date_field,'dd/mm/yyyy')) from table
HK boy
  • 1,398
  • 11
  • 17
  • 25
Youness Marhrani
  • 1,084
  • 16
  • 8
0

I'd go with the following:

Select max(start_ts) 
from db 
where trunc(start_ts) =  date'13-may-2016'
Laladon
  • 1
0

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"T"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"T"HH24:MI:SS:FF3')) From Dual;

It worked for me, hope it will help you as well.

Zdravko Kolev
  • 1,569
  • 14
  • 20
Apeksha Mukati
  • 1
-2

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.

Dinu
  • 845
  • 3
  • 13
  • 27