Date information

[campbere]

I need to get the correct syntax for adding months to a date. I want to select the max(date) - 6 months.

I tried select (max(date) - 6MM) but that failed. The one Oracle book I have doesnt discuss this.

Thanks for your help.

 

[THoey]

I am pretty sure that Oracle lets you do addition and subtraction by days, so try:

SELECT (MAX(YOURDATE)) - 180
FROM my_table;

Hope that helps...


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[karluk]

Oracle has an add_months function, so

add_months(max(date),-6)

should do the trick.

 

[THoey]

Forgot about that one Karluk.

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[manojbajaj]

It's very simple Pleas use this

Add_months(max(date),- 6 ) from .....

Manoj Bajaj

 

[Kavy]

Hi !

This should work:
select add_months(to_date(max(YourDate)), -6) from <table_name>;

The other previous will give u error : invalid character.
To avoid this error, convert the max(yourdate) into date first and then use the add_months function.

thx,
Kavy