Date Calculation....

[ChiTownDiva]

I want to calculate a hire date 55 days back. I know you can use ADD_Months to calculate months if you have days, but how do calculate days.

I want to say

Person.hire_dt2 <= CurrentDate - 55

Also, how do I turn on the Query Exection Plan function?

 

[ThomVF]

If you subtract an integer from a date, it will give you that many days back.

From ORACLE TechNet docs:
You can add and subtract number constants as well as other dates from dates. Oracle interprets number constants in arithmetic date expressions as numbers of days. For example, SYSDATE + 1 is tomorrow. SYSDATE - 7 is one week ago. SYSDATE + (10/1440) is ten minutes from now. Subtracting the hiredate column of the emp table from SYSDATE returns the number of days since each employee was hired. You cannot multiply or divide DATE values.


=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[carp]

However, since SYSDATE includes hours, minutes, and seconds, you will probably want to truncate the date:

WHERE hire_date <= trunc(sysdate) - 55

 

[ChiTownDiva]

Hi, Carp...

You're right...it did give me minutes, seconds, etc.

But now, I've got a new problem:

PERSON.UPDATED_DT2 <= TRUNC(TO_DATE ('&Last_Date_Updated'), 'MM/DD/YYYY')) -55

Seems like no matter where I put the second parenthesis, I get this error message...

&quot;ORA-00933 SQL command not properly ended&quot;.

What am I doing wrong?

 

[ThomVF]

You have the parens out of order...use this

PERSON.UPDATED_DT2 <=
TRUNC(TO_DATE ('&Last_Date_Updated', 'MM/DD/YYYY') -55)


=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[Bimalaggarwal]

You have one additional closing bracket (I have put empty spaces before and afte it)
--------------------

<= TRUNC(TO_DATE ('&Last_Date_Updated'), 'MM/DD/YYYY') ) -55


Correct =>
--------------------
select TO_CHAR(TRUNC(TO_DATE ('&Last_Date_Updated')) - 55 ,'MM/DD/YYYY') from dual;

 

[ChiTownDiva]

When I put in either &quot;07/18/2002&quot; or &quot;7/18/2002&quot;, I get this message...

&quot;ORA-12801: Error signaled in parallel query server P002&quot;
&quot;ORA-01843: Not a valid month&quot;

 

[ThomVF]

Unless you have it set otherwise, the default date format is &quot;dd-mon-rr&quot; (AMERICA/AMERICAN NLS).

Since you are trying to TO_DATE a format of &quot;mm/dd/yyyy&quot;,
you have to use:
TO_DATE(&date_supplied,'mm/dd/yyyy')

In all:
PERSON.UPDATED_DT2 <=
TRUNC(TO_DATE ('&Last_Date_Updated', 'MM/DD/YYYY') -55)

Let's see the actual statement you came up with.
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[ChiTownDiva]

Thanks Dr.Flaherty...it worked!

Me and parenthesis...we don't always see eye to eye!

ChiTownDiva

 

[ThomVF]

=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com