-1

I have a date column which I need to convert to character. But doing so it retrieves '00-000-00' even though the actual data has date value. Also, when I convert it again to date it gives the next day value.

For example: 

Value: 25-MAR-17 (Date Datatype)
TO_CHAR(Value, 'DD-MON-YY'): '00-000-00'
TO_DATE(Value, 'DD-MON-YY'): '26-MAR-17'

And it's not happening for all the values but only the recent values.

Here is the Dump value present in that date column:

select OrderNumber, LoanDate, Dump(LoanDate) from OrderDetail
where OrderNumber=283402 

OrderNumber| LoanDate  | Dump(LoanDate)
283402     | 26-MAR-17 | Typ=12 Len=7: 120,117,3,27,0,15,40

Can you please explain me why these weird things happening and how to handle this?

Vignesh Kiswanth
  • 91
  • 1
  • 1
  • 8
  • 3
    It's pretty hard for us to understand your question, because there is no way this should happen with the data you've presented. Please provide a reproducible test case. That is, a dummy table with sample data and the queries you're running which exhibit this behaviour. Cut down down the details to the essentials. You may find this exercise gives you insight and allows you to solve your problem without our help. – APC Apr 09 '17 at 05:37
  • Date to Date conversion is just to check how it behaves. – Vignesh Kiswanth Apr 09 '17 at 07:51
  • 2
    Please provide a [MCVE] with the DDL and DML statements necessary to replicate the problem. Please also try `SELECT DUMP( your_date_column ) FROM your_table` and post what the value is. – MT0 Apr 09 '17 at 09:43
  • You've edited your question but added no new substantial information. If you want a proper answer you're going to have to post some data which allows us to reproduce and investigate your issue. – APC Apr 09 '17 at 11:49
  • What do you get from `DUMP(value)`? – Wernfried Domscheit Apr 09 '17 at 17:13
  • select OrderNumber, LoanDate, Dump(LoanDate) from OrderDetail where OrderNumber=283402 OrderNumber|LoanDate|Dump(LoanDate) 283402|26-MAR-17|Typ=12 Len=7: 120,117,3,27,0,15,40 – Vignesh Kiswanth Apr 10 '17 at 04:46
  • I've been trying to insert a date value that comes up with that dump without success. `create table t (d date); insert into t values (to_date('26/03/2017 23:59:59','DD/MM/YYYY HH24:MI:SS')); insert into t values (to_date('27/03/2017 00:00:00','DD/MM/YYYY HH24:MI:SS'));select dump(d) from t;` returns `Typ=12 Len=7: 120,117,3,27,1,1,1 and Typ=12 Len=7: 120,117,3,26,24,60,60`. I suspect your data might have been corrupted, which might cause the behaviour you're seeing. – Jeffrey Kemp Apr 10 '17 at 07:09
  • I think a zero (0) in that byte is invalid for an Oracle date, as Oracle stores the hour+1 there. – Jeffrey Kemp Apr 10 '17 at 07:10
  • Refer http://stackoverflow.com/questions/13568193/how-are-dates-stored-in-oracle. – Jeffrey Kemp Apr 10 '17 at 07:12

1 Answers1

0

Putting as answer instead of comment, as it would be too big for comment

What you are saying about 0 is not possible. You might get zeroes, if you have set NLS_DATE_FORMAT to display only time. Also you might get next date due to timezone conversion.

To troubleshoot it follow these steps, edit the question, and give the output in the question.

  1. SELECT * FROM nls_session_parameters;

  2. Run select sysdate from dual; and show the output.

  3. Run this alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
  4. Run select sysdate from dual and show the output.
  5. Now select your date again, select <date column> from <your table> where rownum<2;
  6. Run select to_char(date_column,'DD-MON-YYYY') from your_table where row_num<2;
Utsav
  • 7,914
  • 2
  • 17
  • 38