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
enrique_aeoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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?
0
johnsoneSenior Oracle DBACommented:
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.
1
enrique_aeoAuthor Commented:
PROCEDURE MostrarReporte(k_fecha_reg in date
.
.
.
    FROM solicitud s
   WHERE s.fecreg = k_fecha_reg
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

johnsoneSenior Oracle DBACommented:
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.
0
slightwv (䄆 Netminder) Commented:
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
0
slightwv (䄆 Netminder) Commented:
If you want to pass in a string in MM/DD/YYY format:

PROCEDURE MostrarReporte(k_fecha_reg in varchar2
.
.
.
    FROM solicitud s
     WHERE s.fecreg >= to_date(k_fecha_reg,'MM/DD/YYYY') and s.fecreg < to_date(k_fecha_reg,'MM/DD/YYYY')+1
0
johnsoneSenior Oracle DBACommented:
I don't recommend sending in a DATE as a character.  Then the procedure needs to handle the conversion from string to date and then any errors that come out of that conversion.
1
slightwv (䄆 Netminder) Commented:
The string to date conversion likely needs to happen somewhere.  From a website, it is entered as a string etc...

It boils down to where the conversion happens.  Many times I find it easier to let people pass my a varchar2 than it is to explain to them how to convert the string to a date on their side.
0
johnsoneSenior Oracle DBACommented:
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.
1
enrique_aeoAuthor Commented:
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
0
slightwv (䄆 Netminder) Commented:
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?
0
enrique_aeoAuthor Commented:
The sp will be invoked from a web application containing a calendar, the dates are in dd / mm / yyy format
0
slightwv (䄆 Netminder) Commented:
>>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.
0
enrique_aeoAuthor Commented:
How do I correct it?
You need to call it with the same date format that the web app will use.
0
slightwv (䄆 Netminder) Commented:
>>How do I correct it?

When testing the procedure, use a date in the correct format.  I don't know what else I can tell you.

If you want the TO_DATE call to use 'MM/DD/YYYY' format, your testing will need to pass in a string in 'MM/DD/YYYY' format.

'13/03/2017' is NOT in 'MM/DD/YYYY' format since there are only 12 months in a year and the value for month (MM) is 13.

If you want to test using March, 13, 2017 in 'MM/DD/YYYY' format that is '03/13/2017'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enrique_aeoAuthor Commented:
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 ;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.