Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how convert to_date oracle in dd/mm/yyyy format

Posted on 2009-04-29
14
Medium Priority
?
4,988 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:tenriquez39
  • 9
  • 4
14 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 24265936
select to_char(TO_DATE(RZDMTJ,'J') ,'dd/mm/yyyy')  AS FechaCobroe,
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24265945
using the explicit mask with to_char will return the right format.

however your julian values are in the 45th century and beyone
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24265967
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:tenriquez39
ID: 24270006
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 24270068
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 24270092
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
 
LVL 7

Expert Comment

by:Piloute
ID: 24270124
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 24270145
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
 

Author Comment

by:tenriquez39
ID: 24270339
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 24270437
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
 

Author Comment

by:tenriquez39
ID: 24271198
i have an access query also in a jdedwards consult show me that day

i attach both screens
query-access.jpg
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 24271374
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
 

Author Closing Comment

by:tenriquez39
ID: 31576241
sdstuber you're great thanks allot

allot allot
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24271889
glad I could help
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question