OraFAQ Forum: SQL & PL/SQL » Taking months off sysdate

Home » SQL & PL/SQL » SQL & PL/SQL » Taking months off sysdate (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Taking months off sysdate [message #445051]

Thu, 25 February 2010 08:03

rosswelsh
Messages: 42
Registered: February 2010
Location: Sunderland

Member

Hi all I was hoping somebody could help.

I have a table called transaction_dw and I need to select all records that have an account balance that has been below 0 in the past 6 months.

The initial query I tried was:

select account_balance, timestamp
from transaction_dw
where account_balance < 0
and timestamp between sysdate and sysdate - 6;

but this is only taking 6 days off the sysdate rather than months, any idea how I can get it to take off 6 months?

Thanks very much.

Report message to a moderator

Re: Taking months off sysdate [message #445052 is a reply to message #445051]

Thu, 25 February 2010 08:05

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

If 6 means 6 days, then why not use 180?

Or look at the function ADD_MONTHS.

and nothing can be between a higher number and lower number. For example, there is no such number that is between 10 and 5.

[Updated on: Thu, 25 February 2010 08:06]

Report message to a moderator

Re: Taking months off sysdate [message #445056 is a reply to message #445051]

Thu, 25 February 2010 08:21

Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> select sysdate, add_months(sysdate,-6) earlier from dual;
SYSDATE             EARLIER
------------------- -------------------
25/02/2010 15:21:47 25/08/2009 15:21:47

Regards
Michel

Report message to a moderator

Re: Taking months off sysdate [message #445057 is a reply to message #445051]

Thu, 25 February 2010 08:23

rosswelsh
Messages: 42
Registered: February 2010
Location: Sunderland

Member

Thanks for your help guys!

Report message to a moderator

Previous Topic:	Date search in sql
Next Topic:	oracle enterprise user

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 18 20:24:43 CST 2026