0

My problem is related to Date type, I am using below code when I want assign a date to a column. D_REPORT := to_date(sysdate,'DD/MM/YYYY HH:MI:SS AM');

but sometimes I receive a date like 31/01/0013 12:24:36 !!

I don't know why sometimes year will appear like 0013 and it is just for few systems, even when I am using sysdate, same problem will occur for me.

I want to check with you, anyone else also having same problem? how I can solve it? because even when I use to_date or just simply write sysdate same problem will happens for me and I don't know what is root cause of the problem.

I am using oracle form 6i and my database is oracle 9i.

Amir
  • 1,919
  • 8
  • 53
  • 105
  • 1
    See these (not exact duplicates, but same issue) http://stackoverflow.com/questions/9147393/comaparing-date-with-sysdate-in-oracle/9147562#9147562, http://stackoverflow.com/questions/14108022/to-date-function-with-sysdate/14108132#14108132, http://stackoverflow.com/questions/14108022/to-date-function-with-sysdate – Lord Peter Jan 09 '13 at 07:49

1 Answers1

3

You shouldn't use to_date on a date (and sysdate is a date).

TO_DATE gets a varchar2 as a parameter so oracle implicitly converts your date (sysdate) to a varchar2 according to NLS_DATE_FORMAT which may not be 'DD/MM/YYYY HH:MI:SS AM' in every environment.

If the NLS_DATE_FORMAT is 'DD/MM/YY HH:MI:SS AM' then you'll get year=0013, since what will really be happening is something like this:

SELECT  to_date(TO_CHAR(sysdate, 'DD/MM/YY HH:MI:SS AM'),'DD/MM/YYYY HH:MI:SS AM')
FROM dual;

So, you just need to insert sysdate and when you display it (convert it to a string) use to_char(sysdate,'DD/MM/YYYY HH:MI:SS AM')

Here is a sqlfiddle demo

A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
  • So you recommend me only use SYSDATE or to_date(TO_CHAR(sysdate, 'DD/MM/YYYY HH:MI:SS AM'),'DD/MM/YYYY HH:MI:SS AM') when I insert my data? I dont want when I get query from my database it appear like 0013. – Amir Jan 09 '13 at 06:56
  • 2
    If your column's datatype is date then you **need** to insert just `sysdate` (it's already a date), no need to do the ` to_date(TO_CHAR(...` – A.B.Cade Jan 09 '13 at 07:07