1

I have a table with a LOAD_STRT_DTM colum. This is a date column and values are like this - 18-JUL-14 08.20.34.000000000 AM.

I want to find the data which came before 5 days.

My logic is - 

Select * from Table where 24 *(To_DATE(Sysdate,'DD-MM-YY') - To_DATE(LOAD_STRT_DTM,'DD-MM-YY')) >120

The issue is - 

Select (To_DATE(Sysdate,'DD-MM-YY') - To_DATE(LOAD_STRT_DTM,'DD-MM-YY')) from table

This query should give the NumberOfDays between two dates. But this is not working, I Doubt, the issue is because of the format of the LOAD_STRT_DTM colum.

Please let me know where i am doint it wrong.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Tushar Kesarwani
  • 77
  • 1
  • 1
  • 14
  • Just FYI, if the above query worked, it would give you records older than 5 days, not more recent than 5 days (as you put it, "before"). – David Faber Mar 18 '15 at 14:06

4 Answers4

1

If your column is DATE datatype everything is ok, just shoot an:

select * from table where LOAD_STRT_DTM > sysdate - 5;

No need to convert dates to DATE datatype.

Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
1

(To_DATE(Sysdate,'DD-MM-YY') - To_DATE(LOAD_STRT_DTM,'DD-MM-YY'))

You don't have to convert a DATE into a DATE again. IT is already a DATE. You just need to use it for date calculations. You use TO_DATE to convert a STRING into a DATE.

For example, if you have a string value like '18-JUL-14', then you would need to convert it into date using TO_DATE. Since your column is DATE data type, you just need to use as it is.

This is a date column

I want to find the data which came before 5 days.

Simply use the filter predicate as:

WHERE load_strt_dtm > SYSDATE - 5;

NOTE : SYSDATE has both date and time elements, so it will filter based on the time too. If you want to use only the date part in the filter criteria, then you could use TRUNC. IT would truncate the time element.

I have answered a similar question, have a look at this https://stackoverflow.com/a/29005418/3989608

Community
  • 1
  • 1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
0

It looks like LOAD_STRT_DTM is a TIMESTAMP rather than a DATE, given the number of decimal points following the seconds. The only thing you have to be cautious about is that Oracle will convert a DATE to a TIMESTAMP implicitly where one of the operands is a TIMESTAMP. So the solution

WHERE load_strt_dtm > SYSDATE - 5

will work; as will

WHERE load_strt_dtm + 5 > SYSDATE

but the following will not:

WHERE SYSDATE - load_start_dtm < 5

the reason being that TIMESTAMP arithmetic produces an INTERVAL rather than a NUMBER.

David Faber
  • 12,277
  • 2
  • 29
  • 40
-3

first convert two dates to same format select datediff(dd,convert(varchar(20),'2015-01-01',112),convert(varchar(20),'01-10-2015',112))

Arockia Raj
  • 1
  • 2