how convert to_date oracle in dd/mm/yyyy format

i'm making query sql oracle

it give me a result to converte  a date field, but it's a bad value

could someone help me please

thks allot

I OBTAIN DATE FIELD RESULT AS : 09/06/16

THE REAL DATE I MUST TO OBTAIN IS 02/01/09  (DD/MM/YY)

YOU COULD SEE ORIGINAL VALUE IN FIELD "FECHACOBRO" AT THE RESULT PANEL


THANKS


SELECT TO_DATE(RZDMTJ,'J') 
  AS FechaCobroe,
 RZDMTJ AS FechaCobro, PRODDTA.F03B14.RZCKNU AS NumCobro,
 PRODDTA.F03B14.RZDOC AS Documento, PRODDTA.F03B13.RYALPH AS Descripcion, PRODDTA.F03B14.RZDCT AS TipDocumento, PRODDTA.F03B14.RZAN8 AS NoDireccion,
 RZDGJ AS FechaContabilizacion, 
RZDICJ AS FechaBatch,
 PRODDTA.F03B14.RZICU AS NoBatch, PRODDTA.F03B14.RZICUT AS TipBatch, RZPAAP/100 AS MontoCobro, PRODDTA.F03B14.RZDOCG AS DocumentoInterno, 
PRODDTA.F03B14.RZRMK AS Comentarios,
 PRODDTA.F03B14.RZVRE AS CodAnulacion, RZUPMJ AS FechaCreacion, 
PRODDTA.F03B14.RZGLC AS Compens_en_LM
FROM (PRODDTA.F03B14 INNER JOIN PRODDTA.F03B13 ON PRODDTA.F03B14.RZPYID = PRODDTA.F03B13.RYPYID) LEFT JOIN PRODDTA.F0901 
ON PRODDTA.F03B13.RYGLBA = PRODDTA.F0901.GMAID  where PRODDTA.F03B14.RZDOC = 175599

Open in new window

conversion-TO-DATE-ORACLE.jpg
tenriquez39EngenierAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
so, your taking the 2nd and 3rd characters as the last two digits of the year
and the 4th,5th and 6th characters as number of days into the year.
and you ignore the first character completely?

that's not a julian day conversion, that's substring parsing, you need to do the same thing in your oracle query....

SELECT TO_CHAR(
           TRUNC(TO_DATE(SUBSTR(rzdmtj, 2, 2), 'RR'), 'yyyy') + SUBSTR(rzdmtj, 4, 3) - 1,
           'dd/mm/yyyy'
       )  AS fechacobroe,
0
 
sdstuberCommented:
select to_char(TO_DATE(RZDMTJ,'J') ,'dd/mm/yyyy')  AS FechaCobroe,
0
 
sdstuberCommented:
using the explicit mask with to_char will return the right format.

however your julian values are in the 45th century and beyone
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sdstuberCommented:
ooops, sorry left off an important piece of info.  45th century BC

modern dates should be in the 2 million range.

You can test with this...

SELECT   TO_CHAR(SYSDATE, 'j'),
         TO_CHAR(TO_DATE(108274, 'j'), 'yyyy-mm-dd ad'),
         TO_CHAR(TO_DATE(108366, 'j'), 'yyyy-mm-dd ad'),
         TO_CHAR(TO_DATE(109022, 'j'), 'yyyy-mm-dd ad')
  FROM   DUAL
0
 
tenriquez39EngenierAuthor Commented:
i made both options still don't function right

option 1)
to_char(TO_DATE(RZDMTJ,'J') ,'dd/mm/yyyy')  AS FechaCobroe,


FECHACOBRO FECHACOBRO NUMCOBRO                   DOCUMENTO DESCRIPCION                              TI NODIRECCION FECHACONTABILIZACION FECHABATCH    NOBATCH TI MONTOCOBRO DOCUMENTOINTERNO COMENTARIOS                    COD FECHACREACION COMP
---------- ---------- ------------------------- ---------- ---------------------------------------- -- ----------- -------------------- ---------- ---------- -- ---------- ---------------- ------------------------------ --- ------------- ----
09/06/4416     108274 REP. 001                      175599 WAL*MART DE MEXICO, S. DE R.L.           FA      161520               108274     108276      56583 RB    -8662.5            17247 Sales Order53116                          108343 NAT
09/09/4416     108366 56027                         175599 CORPORATIVO DE BEBIDAS Y LIQUI           FA      161520               108366     109005      67880 RB   -95089.5            18120 Sales Order53116                          109008 NAT
07/06/4414     109002 56028                         175599 CORPORATIVO DE BEBIDAS Y LIQUI           FA      161520               109022     109022      70351 RB   -95089.5            18624 Sales Order53116                          109026 NAT


option 2)TO_CHAR(to_date(RZDMTJ, 'j')) AS FechaCobroe,

FECHACOB FECHACOBRO NUMCOBRO                   DOCUMENTO DESCRIPCION                              TI NODIRECCION FECHACONTABILIZACION FECHABATCH    NOBATCH TI MONTOCOBRO DOCUMENTOINTERNO COMENTARIOS                    COD FECHACREACION COMP
-------- ---------- ------------------------- ---------- ---------------------------------------- -- ----------- -------------------- ---------- ---------- -- ---------- ---------------- ------------------------------ --- ------------- ----
09/06/16     108274 REP. 001                      175599 WAL*MART DE MEXICO, S. DE R.L.           FA      161520               108274     108276      56583 RB    -8662.5            17247 Sales Order53116                          108343 NAT

option 3)TO_CHAR(TO_DATE(RZDMTJ, 'j'), 'yyyy-mm-dd ad') as fechacobroe

FECHACOBROE   FECHACOBRO NUMCOBRO                   DOCUMENTO DESCRIPCION                              TI NODIRECCION FECHACONTABILIZACION FECHABATCH    NOBATCH TI MONTOCOBRO DOCUMENTOINTERNO COMENTARIOS                    COD FECHACREACION COMP
------------- ---------- ------------------------- ---------- ---------------------------------------- -- ----------- -------------------- ---------- ---------- -- ---------- ---------------- ------------------------------ --- ------------- ----
4416-06-09 AC     108274 REP. 001                      175599 WAL*MART DE MEXICO, S. DE R.L.           FA      161520               108274     108276      56583 RB    -8662.5            17247 Sales Order53116                          108343 NAT


someone could help me please

thks allot


0
 
sdstuberCommented:
sorry, I guess I didn't explain well enough.

this isn't a date format problem at all.

it's a value conversion problem.

How is  108274 supposed to equal January 2, 2009?

108274 isn't a julian day value.  It's something else.  What is it?  Once we know the function to convert that value into a date, it's trivial to format the date in dd/mm/yyyy
0
 
sdstuberCommented:
or, if 108274 is supposed to julian, then it's not a day in 2009 AD,  it's a day in 4416 BC.

the script above shows what the julian days are for each of the 3 values you've shown.

0
 
PilouteCommented:
Hi.

Your output is text. In other words, if you need a special formatting, you have to include whatever date you get in a to_char() function with the right format template.

Example :

select to_char(sysdate,'DD:MM:YY') from dual;

'sysdate' can be whatever is a date specification. Your example would be :

SELECT TO_CHAR(TO_DATE(RZDMTJ,'J'),'DD/MM/YY') AS FechaCobroe,...

                                                                             Î
                                                           here is the formatting for the date

Cheers,
P
0
 
sdstuberCommented:
look above in the first response, that's already been shown, however the problem isn't in formatting it's the values themselves are either NOT julian values or they are julian values for the wrong dates.

0
 
tenriquez39EngenierAuthor Commented:
excuse me stdstuber

also i don't explain enough

i don't say 108274 i't's a 2009 day

i refer to 109022    
109022 is real 2009 date

i have an access query (obviously.....other tecniq scenario etc) accesing same oracle DB it run fine and give correct date

thanks
0
 
sdstuberCommented:
from your original example you did say 108274 was supposed to be 02/01/09
when it was showing 09/06/16 for 108274.


but regardless,  what day do you think 109022 should be?

and please explain how you arrive at that date.  What are you adding/subtracting or extracting from that number to generate the date you think it should be?



0
 
tenriquez39EngenierAuthor Commented:
i have an access query also in a jdedwards consult show me that day

i attach both screens
query-access.jpg
0
 
tenriquez39EngenierAuthor Commented:
sdstuber you're great thanks allot

allot allot
0
 
sdstuberCommented:
glad I could help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.