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
Oracle Database

Avatar of undefined
Last Comment
enrique_aeo

8/22/2022 - Mon
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?
johnsone

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.
enrique_aeo

ASKER
PROCEDURE MostrarReporte(k_fecha_reg in date
.
.
.
    FROM solicitud s
   WHERE s.fecreg = k_fecha_reg
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
johnsone

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.
slightwv (䄆 Netminder)

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
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
johnsone

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
enrique_aeo

ASKER
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
slightwv (䄆 Netminder)

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?
enrique_aeo

ASKER
The sp will be invoked from a web application containing a calendar, the dates are in dd / mm / yyy format
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
slightwv (䄆 Netminder)

>>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.
enrique_aeo

ASKER
How do I correct it?
You need to call it with the same date format that the web app will use.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
enrique_aeo

ASKER
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 ;
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.