OraFAQ Forum: SQL & PL/SQL » Getting ORA-01839: date not valid for month specified

Home » SQL & PL/SQL » SQL & PL/SQL » Getting ORA-01839: date not valid for month specified (Oracle 11g)

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

Getting ORA-01839: date not valid for month specified [message #646471]

Thu, 31 December 2015 11:12

shuklajayb4
Messages: 2
Registered: December 2015

Junior Member

Hi All,

This might be nirmal issue, but not able to find out appropriate solution for this, I have requirement where user will specify number of month to pass as a user argument and based on that value SQL qyery will run to take data before those value.

For e.g. If a user wants to pull a data before 1 month and if the program executes today(Dec 31 2015), it calculates date as 31 Nov. 2015 which is invalid.

Query I am executing is as below -

SELECT TO_CHAR(sysdate - interval '1' month, 'MMDDYYYY') from dual

Where '1' is value passed by user.

Can anyone please provide me guidance how to solve this issue using SQL query?

Regards,
Jay Shukla

Report message to a moderator

Re: Getting ORA-01839: date not valid for month specified [message #646473 is a reply to message #646471]

Thu, 31 December 2015 11:16

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

orclz>
orclz> SELECT TO_CHAR(add_months(sysdate,-1),'MMDDYYYY') from dual ;

TO_CHAR(
--------
11302015

orclz>

Report message to a moderator

Re: Getting ORA-01839: date not valid for month specified [message #646475 is a reply to message #646473]

Thu, 31 December 2015 11:58

Bill B
Messages: 1971
Registered: December 2004

Senior Member

However if you are pulling data from a table with a date column you want the test to be

where my_date_column <= add_months(trunc(sysdate),-1)

and if you want to start on the last date of the previous month (for example)

where my_date_column < trunc(sysdate,'MONTH')

[Updated on: Thu, 31 December 2015 13:33]

Report message to a moderator

Re: Getting ORA-01839: date not valid for month specified [message #646477 is a reply to message #646475]

Thu, 31 December 2015 13:30

shuklajayb4
Messages: 2
Registered: December 2015

Junior Member

Thanks for your response !!!
I am trying to calculate date value from dual table only.

Regards,
Jay Shukla

Report message to a moderator

Re: Getting ORA-01839: date not valid for month specified [message #646480 is a reply to message #646477]

Thu, 31 December 2015 19:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

shuklajayb4 wrote on Thu, 31 December 2015 11:30

Thanks for your response !!!
I am trying to calculate date value from dual table only.

Regards,
Jay Shukla

DUAL table does not contain any DATE

Report message to a moderator

Re: Getting ORA-01839: date not valid for month specified [message #646543 is a reply to message #646477]

Mon, 04 January 2016 03:00

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

shuklajayb4 wrote on Thu, 31 December 2015 19:30

Thanks for your response !!!
I am trying to calculate date value from dual table only.

Not according to this:
shuklajayb4 wrote on Thu, 31 December 2015 17:12

I have requirement where user will specify number of month to pass as a user argument and based on that value SQL qyery will run to take data before those value.

Anyway, John and Bill have given you what you need.

Report message to a moderator

Previous Topic:	Help me on Syntax of PL/SQL
Next Topic:	Collections- Associative arrays

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 23:10:59 CDT 2024