Convert date entry with oracle

1

I'm having trouble returning the results of the following query, where I put the & month variable, if I put '01 -sep-2017 'would bring the results I need, but in the dialog box I wanted the user is typed with the patterns used for date, being the correct '01 / 09/2017 ', however entering that date is presented the error ORA-01858: a non-numeric character was found where numeric was expected

Select a.pro_st_alternativo,
   a.pro_st_descricao,
   mgadm.adm_pck_estoque.F_SALDO_QTDE(20,21,a.pro_pad_in_codigo,a.pro_in_codigo,1,null,null,to_char(to_date(&mes), 'dd/mm/yyyy'),'AN')Saldo_mes,

   a.uni_st_unidade,
   a.pro_bo_controlalotes,
   a.pro_st_cestoque,
   a.pro_st_defitem,
   A.PRO_CH_DEFFISCALITEM,
   A.PRO_ST_ORIGEM

From mgadm.est_produtos a
Where A.PRO_PAD_IN_CODIGO = 4;
    
asked by anonymous 18.10.2017 / 14:35

1 answer

0

There is a function in PL / SQL called to_date . It is responsible for performing this conversion, thus:

to_date(&mes, 'DD/MM/YYYY')

Some examples of masks:

TO_DATE('2003/07/09', 'yyyy/mm/dd')
retorna: July 9, 2003

TO_DATE('070903', 'MMDDYY')
Retorna: July 9, 2003

TO_DATE('20020315', 'yyyymmdd')
Retorna: Mar 15, 2002

You can find the complete list of masks here .

    
18.10.2017 / 19:28