We help IT Professionals succeed at work.

format dd/mm/yyyy parameter

enrique_aeo
enrique_aeo asked
on
188 Views
Last Modified: 2017-03-13
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

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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
CERTIFIED EXPERT

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
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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 ;

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions