ORA-01843: Not a valid month
Moderators: chulett, rschirm, roy
ORA-01843: Not a valid month
Hello
I was using the below query to select max date from source which alos have null values.
select to_char(nvl(max(col_date), to_date('10/10/2001 10:10:10'))) max_date from table_name
This is working fine from toad, but when used in OCI stage returned ORA-01843. nota a valid month. (i think i am commiting very small mistake)
Thanks in advance.
reddy.
I was using the below query to select max date from source which alos have null values.
select to_char(nvl(max(col_date), to_date('10/10/2001 10:10:10'))) max_date from table_name
This is working fine from toad, but when used in OCI stage returned ORA-01843. nota a valid month. (i think i am commiting very small mistake)
Thanks in advance.
reddy.
-
- Charter Member
- Posts: 193
- Joined: Tue Sep 05, 2006 8:01 pm
- Location: Australia
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
I am sure that the above mentioned query is not running in toad as it is.
The default date format/mask for to_date() function is dd/mon/yyyy or dd/mon/yy (any seperator, without time part). The input date has a different mask as 'dd/mm/yyyy hh24:mi:ss'.
similarly to_char() will return in format dd-mon-yy unless the mask is specified.
The default date format/mask for to_date() function is dd/mon/yyyy or dd/mon/yy (any seperator, without time part). The input date has a different mask as 'dd/mm/yyyy hh24:mi:ss'.
similarly to_char() will return in format dd-mon-yy unless the mask is specified.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.![Wink :wink:](./images/smilies/icon_wink.gif)
Genius may have its limitations, but stupidity is not thus handicapped.
![Wink :wink:](./images/smilies/icon_wink.gif)
Re: ORA-01843: Not a valid month
ORA-01843. nota a valid month. (i think i am commiting very small mistake)
What is the data type you have defined for the field, If it is date then chagne it to Varchar, For date data type system expecting data in 'dd-mon-yyyy' format.
Regards,
Nitin Swarnakr
What is the data type you have defined for the field, If it is date then chagne it to Varchar, For date data type system expecting data in 'dd-mon-yyyy' format.
Regards,
Nitin Swarnakr
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Roger that.
not a valid month with current date format provided should come when month is defined as "MON" in NLS_DATE_FORMAT. Also it will should run from datastage as well if running from toad (as i cannot see any other complication in this query). Unless you are using two different servers.![Wink :wink:](./images/smilies/icon_wink.gif)
![Smile :)](./images/smilies/icon_smile.gif)
not a valid month with current date format provided should come when month is defined as "MON" in NLS_DATE_FORMAT. Also it will should run from datastage as well if running from toad (as i cannot see any other complication in this query). Unless you are using two different servers.
![Wink :wink:](./images/smilies/icon_wink.gif)
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.![Wink :wink:](./images/smilies/icon_wink.gif)
Genius may have its limitations, but stupidity is not thus handicapped.
![Wink :wink:](./images/smilies/icon_wink.gif)
Thansks alot guys..
select to_char(nvl(max(col_date), to_date('10/10/2001 10:10:10' , 'MM/DD/YYYY HH24:MI:SS'))) as max_date from table_name; When i run this query in Toad returns with exact MAX date with time as well(08/14/2009 11:30:44) But datastage returns like this (08/14/2009 00:00:00). Datastage max_date Type is Timestamp.
How i can get the max_date with time precision.
Thanks in advance.
select to_char(nvl(max(col_date), to_date('10/10/2001 10:10:10' , 'MM/DD/YYYY HH24:MI:SS'))) as max_date from table_name; When i run this query in Toad returns with exact MAX date with time as well(08/14/2009 11:30:44) But datastage returns like this (08/14/2009 00:00:00). Datastage max_date Type is Timestamp.
How i can get the max_date with time precision.
Thanks in advance.
You fixed the TO_DATE() but not the TO_CHAR(). As noted previously, both need a proper mask. Toad let's you get away with all kinds of sins because of its "default" system. Better to compare sqlplus to DataStage as that's more of an "apples to apples" comparison.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 151
- Joined: Fri Feb 13, 2009 4:19 pm