January 5, 2011 at 2:58 pm
Please tell me the appropriate where condition with dates in oracle
select * from table_name
where Date_value between getdate() and (getdate-6months)
January 9, 2011 at 7:13 am
varunkum (1/5/2011)
Please tell me the appropriate where condition with dates in oracleselect * from table_name
where Date_value between getdate() and (getdate-6months)
Try this:
select * from table_name
where Date_valus between sysdate and add_months(sysdate,-6)
Danilo
January 9, 2011 at 9:47 am
Building on Danilo's answer - please remember SYSDATE() returns the date down to the "second" therefore if you want whole dates you have to truncate the date by including TRUNC() function.
If this is the case, you want to truncate both dates into variables then use the variables on your BETWEEN condition - the use of TRUNC() function on the predicate will prevent the use of any existing index on such a column.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply