Oracle Datatype Error

I'm trying to insert a value into date datatype by selecting a value from a source table whose column is also date datatype. I have selected the column directly without doing any c

Solution 1:

I assume you try to get the dates truncated to the day into the Target table. This can be done by doing so:

insertinto Target(Targetdate) select trunc(Source.START_DATE,'DD') from Source

EDIT

Dazzal mentioned that as this is the default operation when leaving out the second parameter to TRUNC, so this is even a bit simpler:

insert into Target(Targetdate) select trunc(Source.START_DATE) from Source

The problem with your query was that Source.START_DATE is a date, not a string...

EDIT2

As it seems that you want to get date strings in a specific format - which has nothing to do with the date type, and how thigs are stored internally - just do this:

SELECT to_char(START_DATE,'YYYY-MM-DD'from Source;

• Oracle TRUNC

Solution 2:

You said you are selecting a date column from source table. When it's date column, then why are you converting again into date? Of course it will give error also..

insertinto Target(Targetdate) 
select to_date(Source.START_DATE,'yyyy-mm-dd'), from Source

Input parameter of 'to_date' function is 'string'

to_date( string1, [ format_mask ], [ nls_language ] )

UPDATE1:

alter session set nls_date_format ='yyyy-mm-dd';


insertinto Target(Targetdate) 
select to_char(Source.START_DATE,'yyyy-mm-dd') from Source

Solution 3:

"example of my source value is 11-JUN-13 and i need target value as YYYY-MM-DD how can i achieve this? "

All Oracle dates are stored in the same internal format:

SQL>select dump(sysdate) from dual;

DUMP(SYSDATE)
---------------------------------------------
Typ=13 Len=8: 221,7,1,3,10,22,8,0SQL>

The display date is entirely dependent on the client environment's default values, or the format mask if we're using TO_CHAR().

If both your columns are the DATE datatype you have overcomplicated things: you do not need to cast the values in the INSERT.

insertinto Target (Targetdate) 
select Source.START_DATE
from Source

You just need to apply a format mask when selecting the target date from Target.