Link to home
Start Free TrialLog in
Avatar of enrique_aeo
enrique_aeo

asked on

ORA-01843: not a valid month - use NVL

Hi experts

i have this error ORA-01843: not a valid month, this is the query
select i.visita_id,
       it.num_documento,
       it.tipo_ingreso,
       it.nombre_completo,
i.create_date,
       to_char(i.create_date, 'HH24:MI') as fecha_Registro,
i.ENTRY_DATE,
       NVL(i.ENTRY_DATE,to_char(i.ENTRY_DATE, 'HH24:MI')) as fecha_Ingreso
  from PORT_INGRESO_TIPO it
  join PORT_INGRESO i
    on it.ingreso_tipo_id = i.transportista_id

the erros has in this line
       NVL(i.ENTRY_DATE,to_char(i.ENTRY_DATE, 'HH24:MI')) as fecha_Ingreso

the data is
23/05/2017 08:50:45 a.m.
www.png
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

What is the data type for entry_date?

If a varchar2, are you just wanting to extract the time from that string?
Avatar of enrique_aeo

ASKER

entry_date is DATE
The NVL statement you have says:  If entry_date is null, return a string that represents the time portion of that null field.

If entry_date is null, it should return a null.  I'm guessing that isn't really what you want.


The main way to get a ORA-01843 is when trying to convert a string to a date with either incorrect data in the field or an incorrect format mask.

I'm not sure how that error is being generated from that line if entry_date is a date data type.
That line doesn't make any sense.  If the value in the column is null, then you want to change the format of the column.  It is null so it doesn't have a format.

Disregarding the fact that the second part of the NVL will never get used (a NULL date doesn't get a format), I will explain why you are getting the error.

The second half of the NVL (i.e. the TO_CHAR call).  That returns a character string.

The first half of the NVL contains an actual DATE datatype.

Oracle needs to coerce one of those two so that the data type is the same.  You cannot have a column with two different data types depending on the value.  So, what Oracle chooses to do is convert the string to a DATE.  However, the string that is present cannot be implicitly converted to a date using the default date format (as defined by NLS_DATE_FORMAT), so you get the error.  If you change your query so the second part of the NVL is a DATE (say the actual column for the sake of showing the example):
WITH i 
     AS (SELECT To_date('23/05/2017 08:50:45 am', 'dd/mm/yyyy hh:mi:ss am') 
                entry_date 
         FROM   dual) 
SELECT Nvl(i.entry_date, i.entry_date) AS fecha_Ingreso 
FROM   i; 

Open in new window

I can't tell you the right way to fix the code, but in order to get it to not error, both sides of the NVL should be explicitly made into the same datatype.  Like, this works, but I don't know if it is what you want:
WITH i 
     AS (SELECT To_date('23/05/2017 08:50:45 am', 'dd/mm/yyyy hh:mi:ss am') 
                entry_date 
         FROM   dual) 
SELECT Nvl(i.entry_date, i.entry_date) AS fecha_Ingreso 
FROM   i; 

Open in new window

It still doesn't make any sense because you cannot format a NULL, but it runs.
fot this column is not problem
       to_char(i.create_date, 'HH24:MI') as fecha_Registro,

At first this column entry_date is null, that's why I use NVL, if I'm using it wrong, you can tell me which is the correct way
Sorry, my second example didn't copy correctly.  It should be this:
WITH i 
     AS (SELECT To_date('23/05/2017 08:50:45 am', 'dd/mm/yyyy hh:mi:ss am') 
                entry_date 
         FROM   dual) 
SELECT Nvl(To_char(i.entry_date, 'dd/mm/yyyy hh:mi:ss am'), 
              To_char(i.entry_date, 'HH24:MI')) AS fecha_Ingreso 
FROM   i; 

Open in new window

>>if I'm using it wrong, you can tell me which is the correct way

I cannot.  I don't have your requirements.

What do you want returned if entry_date is null?
i attached the data
www.png
when  entry_date is not null i need show HH24:MI
it's code work for to_char(i.create_date, 'HH24:MI') as fecha_Registro

the problem is entry_date when IN NULL
>>i attached the data

OK, and?  That doesn't help me show what you want returned if entry_date is null.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
So, that would say the first half of the NVL should be:

NVL(to_char(i.ENTRY_DATE, 'HH24:MI'), ???????) as fecha_Ingreso

As stated, we have no idea what you want in the second half of the NVL.  If you know, put it in.
If you remove the NVL and ENTRY_DATE is NULL, you will get NULL returned.  Based on the way the original query is written, I'm guessing if it is NULL they want something else, but apparently the something else is some sort of state secret.
this works
       to_char(i.entry_date, 'HH24:MI') as fecha_Ingreso,

thanks