Link to home
Start Free TrialLog in
Avatar of enrique_aeo
enrique_aeo

asked on

format dd/mm/yyyy parameter

hi experts
i have this value in the database: 10/03/2017 02:25:45 a.m. the column type is date
SELECT *
FROM CN01.MAEVISITAS vs
where
    visfhing>=to_Date('10032017 00:00:00','ddmmyyyy hh24:mi:ss')
order by visfhing DESC

I need to create a sp that has a parameter visfhing_ to send the date of the day in the format dd/mm/yyyy
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I don't understand what you are asking.

Date formats can be controlled by setting nls_date_format or by using TO_CHAR.

What are you wanting a stored procedure to do?
I would create a stored procedure where the parameter is of DATE datatype.  Then force whoever is calling the procedure to supply a DATE.  That way, the procedure doesn't have to worry about checking a string, converting it to a date and errors in that conversion.  It is already known to be valid.
Avatar of enrique_aeo

ASKER

PROCEDURE MostrarReporte(k_fecha_reg in date
.
.
.
    FROM solicitud s
   WHERE s.fecreg = k_fecha_reg
If your parameter to the procedure is DATE datatype, then there is no format.  The caller is required to convert it explicitly or rely on implicit conversion through NLS_DATE_FORMAT.
If you are saying k_fecha_re is a date data type and doesn't have the time portion and you need all dates from the table then try something like this:
...
   FROM solicitud s
   WHERE s.fecreg >= k_fecha_reg and s.fecreg < k_fecha_reg+1
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
SOLUTION
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
SOLUTION
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
Personally, I would rather have them put the TO_DATE conversion in the procedure call.

Or, for a website, where the format would always be the same, set NLS_DATE_FORMAT and rely on the implicit conversion.

Too many things that could go wrong with converting it within the procedure and a lot of extra code needed to validate the string.  I would rather the caller deal with that.  That way if there is an issue with the data it is the caller's problem and not a database problem.  When it really is not a database problem.
this is store procedure

PROCEDURE MostrarVisita(k_fecha_reg in varchar2,
                        k_resultado  out sys_refcursor,
                        k_coderror   out number,
                        k_msgerror   out varchar2)
IS
BEGIN
  OPEN k_resultado FOR
SELECT vs.visfhing, vs.visfhsal, vs.visfecuope, vs.visfecreg, vs.visfhslug
FROM XXXX vs
where
    vs.visfecreg >= to_date(k_fecha_reg,'MM/DD/YYYY')
    and vs.visfecreg < to_date(k_fecha_reg,'MM/DD/YYYY')+1

order by visfhing DESC;

END;

i send  13/03/2017 but i have this error: not a valid month
www.png
That date doesn't match the format mask.

13/03/2017 and MM/DD/YYYY

that makes 13 the month.

I think you want 'DD/MM/YYYY' format masks?
The sp will be invoked from a web application containing a calendar, the dates are in dd / mm / yyy format
>>the dates are in dd / mm / yyy format

Then the error is correct.  You called it with 13/03/2017 which causes an invalid month.  You need to call it with the same date format that the web app will use.
How do I correct it?
You need to call it with the same date format that the web app will use.
ASKER CERTIFIED SOLUTION
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
my mistake

this is sp
  FROM CN01.MAEVISITAS vs
   where vs.visfecreg >= to_date(k_fecha_reg, 'DD/MM/YYYY')
     and vs.visfecreg < to_date(k_fecha_reg, 'DD/MM/YYYY') + 1
--     and vs.visnrodoc = 'Q41885581'
   order by VS.VISFHING ;