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_DAT E, '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_DAT E, 'HH24:MI')) as fecha_Ingreso
the data is
23/05/2017 08:50:45 a.m.
www.png
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
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
the data is
23/05/2017 08:50:45 a.m.
www.png
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.
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):
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;
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;
It still doesn't make any sense because you cannot format a NULL, but it runs.
ASKER
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
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;
>>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 cannot. I don't have your requirements.
What do you want returned if entry_date is null?
ASKER
i attached the data
www.png
www.png
ASKER
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
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.
OK, and? That doesn't help me show what you want returned if entry_date is null.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
this works
to_char(i.entry_date, 'HH24:MI') as fecha_Ingreso,
thanks
to_char(i.entry_date, 'HH24:MI') as fecha_Ingreso,
thanks
If a varchar2, are you just wanting to extract the time from that string?