format dd/mm/yyyy parameter

enrique_aeo
enrique_aeo used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

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?
johnsoneSenior Oracle DBA

Commented:
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.

Author

Commented:
PROCEDURE MostrarReporte(k_fecha_reg in date
.
.
.
    FROM solicitud s
   WHERE s.fecreg = k_fecha_reg
Ensure you’re charging the right price for your IT

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

johnsoneSenior Oracle DBA

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

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
Most Valuable Expert 2012
Distinguished Expert 2018
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
johnsoneSenior Oracle DBA
Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018
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.
johnsoneSenior Oracle DBA

Commented:
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.

Author

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
Most Valuable Expert 2012
Distinguished Expert 2018

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?

Author

Commented:
The sp will be invoked from a web application containing a calendar, the dates are in dd / mm / yyy format
Most Valuable Expert 2012
Distinguished Expert 2018

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.

Author

Commented:
How do I correct it?
You need to call it with the same date format that the web app will use.
Most Valuable Expert 2012
Distinguished Expert 2018
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'

Author

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 ;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial