CODEWITHSUNDEEP
oracle11goracleforms
nishi
nishi

Reputation: 41

How To Insert a Date using oracle form

i use oracle forms 11g. And i pass To_Date(sysdate,'dd-mm-yyyy') that is inserted to the data base and data type of the column is date. but it is inserted as 22-10-0015 instead of 22-10-2015 can you help me?

i use procedure to insert data s and oracle from button click.i need to insert dd-mm-yyyy format to the data base how to do this?

Upvotes: 0

Views: 1391

Answers (2)

pablomatico
pablomatico

Reputation: 2242

SYSDATE is already a DATE. You don't need to use TO_DATE to convert it into a DATE.

What it's happening is that you're converting SYSDATE into a string and you're using the YYYY mask which will translate any year over 2000 into the 00's (that's why 2015 is being converted to the year 15). If you use the RRRR mask you will get the expected result:

TO_DATE(SYSDATE,'dd-mm-rrrr')

However, this is not a good idea as it's unnecesary and could fail if the NLS_DATE_FORMAT model used for the implicit conversion to string doesn't match.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191315

sysdate is already a date, so it doesn't make sense to call to_date() for it. You are implicitly converting it to a string, and then explicitly back to a date. The implicit step is using your Forms session's NLS_DATE_FORMAT, which is presumably DD-MM-YY from the symptoms, so you're really doing:

to_date(to_char(sysdate,'dd-mm-yy'),'dd-mm-yyyy')

The implicit string version would show the year as 15 if you ran that on its own; and the explicit conversion correctly sees that as 0015 rather than assuming 2015.

You should just pass sysdate directly; but if you're trying to strip out the time so it shows as midnight, you can use the trunc() function:

trunc(sysdate)

Upvotes: 2

Related Questions