Home » SQL & PL/SQL » SQL & PL/SQL » Taking months off sysdate (Oracle 10g)
Taking months off sysdate [message #445051] Thu, 25 February 2010 08:03 Go to next message
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.
Re: Taking months off sysdate [message #445052 is a reply to message #445051] Thu, 25 February 2010 08:05 Go to previous messageGo to next message
joy_division
Messages: 4640
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Taking months off sysdate [message #445057 is a reply to message #445051] Thu, 25 February 2010 08:23 Go to previous message
rosswelsh
Messages: 42
Registered: February 2010
Location: Sunderland
Member
Thanks for your help guys!
Previous Topic: Date search in sql
Next Topic: oracle enterprise user
Goto Forum:
  


Current Time: Mon Dec 05 18:58:12 CST 2016

Total time taken to generate the page: 0.14237 seconds