how to minus 2 days from the date field

database consist of:  file_name and post_date
file_name = "abc20050426.txt"
post_date = "28-APR-2005 03:59:48"

I would like to minus 2 days from the post_date, and convert to "yyyymmdd"
then compare it to the date on the file_name.

select file_name, post_date from table
where  to_char(post_date, 'DD-MON-YYYY') >= (sysdate -7)
and ((substr(file_name,4,8) =  to_char(post_date, 'YYYYMMDD') = (sysdate -2));
matgoldAsked:
Who is Participating?
 
jrb1Connect With a Mentor Commented:
Sure, make it:

select file_name||'~'||post_date||'~'||round(1-(post_date-to_date('23-APR-2005 10:00','dd-mon-yyyy hh24:mi')), 2)
from ftp_status
where file_name = 'qorder.xml';
0
 
MikeOM_DBACommented:
Try:

select file_name, post_date from table1
where  post_date >= TRUNC(sysdate -7)
and to_date(substr(file_name,4,8), 'YYYYMMDD') = TRUNC(sysdate -2);
0
 
matgoldAuthor Commented:
and to_date(substr(file_name,4,8), 'YYYYMMDD') = TRUNC(sysdate -2);
      *
ORA-00904: invalid column name

it should be to_char, right?

I change it to to_char, and it error
ORA-01722: invalid number
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
matgoldAuthor Commented:
I got it.

and substr(file_name,21,8) = to_char(TRUNC(post_date -2), 'YYYYMMDD');
0
 
matgoldAuthor Commented:
can you help me out with the calculation?
post_date = "28-APR-2005 03:59:48"
how do I take (24 - substr(post_date,13,5) / 24)
0
 
jrb1Commented:
hmmm...what are you trying to get from this?  If you just want the time portion it's

post_date - trunc(post_date)

That answer will be a fraction of a day.  If you want hours, make it:

(post_date - trunc(post_date) * 24
0
 
earth man2Commented:
To get proportion of the day left rounded to minutes you can use trunc function
viz
trunc( postdate + 1 ) - trunc( post_date, 'MI' )
0
 
matgoldAuthor Commented:
basically, what I'm trying to do is to get the percentage.
if I'm expecting the end_time to be 13:00
but it was posted at 15:00, that would make it 2 hours late.
so, I would take (24-2)/24 = .92
0
 
jrb1Commented:
(24 - (post_date-trunc(post_date))*24+{timeexpected}) / 24
0
 
matgoldAuthor Commented:
file_name = qorder.xml
post_date = 23-APR-2005 11:41:10
timeexpected = 23-APR-2005 10:00


select file_name||'~'||post_date from table
where file_name = 'qorder.xml'
(24 - (post_date-trunc(post_date))*24+{timeexpected}) / 24;

not sure where put your code.
if possible the result from your code, should go at the end.

output should look like so.
qorder.xml~dd-mmm-yyyy hh:mm:ss~.90
0
 
jrb1Commented:
OK, if you have a timestamp for post date and expected, it is just:

select file_name||'~'||to_char(post_date,'dd-mon-yyyy')||'~'|| round(1-(post_date-timeexpected), 2)
from table
where file_name = 'qorder.xml'
0
 
matgoldAuthor Commented:
select file_name||'~'||post_date||'~'||round(1-(post_date-23-APR-2005 10:00), 2)
  2  from ftp_status
  3  where file_name = 'qorder.xml';

Bind variable "00" not declared.
0
 
matgoldAuthor Commented:
alright !!!!!!!

What happen, if I don't want to hardcode the date '23-APR-2005', I want to use the same date as post_date.  something like
 round(1-(post_date-to_date('post_date 10:00','dd-mon-yyyy hh24:mi')), 2)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.