0

I'm trying to find the difference between a TIMESTAMP(6) field and a DATE field in oracle sql to return number of days.

Anyone know how to convert these?

HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
Ahlam Mustafa
  • 11
  • 2
  • 1
    In SO, you must always mention what you have tried and if you have made any code. Please google "oracle date to timestamp" first and try some solutions in the results. If that does not help, then you can come back here. Let me give you an example - http://oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals.php – stack1 Jan 23 '15 at 23:57
  • What happens when you try - you get an interval data type back? Please show your current attempt, the result you get, and the result you expect (by editing the question). – Alex Poole Jan 23 '15 at 23:57
  • The answers to this question show how this can be accomplished. In short, use the `EXTRACT()` function. http://stackoverflow.com/questions/11617962/calculating-difference-between-two-timestamps-in-oracle-in-milliseconds – David Faber Jan 24 '15 at 00:06

1 Answers1

0

i tried to_date, to_number and a few other functions. I actually got it to work by using trunc on my datetimestamp 

(a.date- trunc(b.datetimestamp))
HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
Ahlam Mustafa
  • 11
  • 2
  • `trunc()` is doing an implicit conversion to a DATE type, which you could do explicitly with `cast()`; but it is also losing the time part of the value. Is that really what you want? – Alex Poole Jan 24 '15 at 00:15