?
Solved

how to minus 2 days from the date field

Posted on 2005-04-28
13
Medium Priority
?
2,011 Views
Last Modified: 2010-08-05
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));
0
Comment
Question by:matgold
13 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 13890044
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
 

Author Comment

by:matgold
ID: 13890167
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
 

Author Comment

by:matgold
ID: 13890285
I got it.

and substr(file_name,21,8) = to_char(TRUNC(post_date -2), 'YYYYMMDD');
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:matgold
ID: 13890621
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
 
LVL 25

Expert Comment

by:jrb1
ID: 13890650
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
 
LVL 22

Expert Comment

by:earth man2
ID: 13891071
To get proportion of the day left rounded to minutes you can use trunc function
viz
trunc( postdate + 1 ) - trunc( post_date, 'MI' )
0
 

Author Comment

by:matgold
ID: 13897903
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
 
LVL 25

Expert Comment

by:jrb1
ID: 13898552
(24 - (post_date-trunc(post_date))*24+{timeexpected}) / 24
0
 

Author Comment

by:matgold
ID: 13898733
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
 
LVL 25

Expert Comment

by:jrb1
ID: 13899472
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
 

Author Comment

by:matgold
ID: 13910028
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
 
LVL 25

Accepted Solution

by:
jrb1 earned 150 total points
ID: 13911142
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
 

Author Comment

by:matgold
ID: 13911591
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month15 days, 12 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question