ORA-01843: Not a valid month

[mekrreddy]

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.

[John Smith]

Check your to_date syntax I think you need to define how the date format is.

[chulett]

Neither your TO_CHAR() nor your TO_DATE() functions have the appropriate 'mask' to use for the conversion.

[priyadarshikunal]

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.

[swarnkar]

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

[chulett]

To the last two posters, dates will use and "expect" the NLS_DATE_FORMAT as the default, never assume it will always be DD-MON-YYYY.

[priyadarshikunal]

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.

[mekrreddy]

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.

[chulett]

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.

[dsuser_cai]

check your to_date command.

select to_char(nvl(max(sysdate), to_date('10/10/2001 10:10:10', 'mm/dd/yyyy hh24:mi:ss'))) max_date from dual