SQL & PL/SQL

Hello,

We are running 10g

I have created a table which contains a date field.

I have a stored procedure where declare a variable as DATE, I then save SYSDATE into it.

v_RUN_DATE DATE;
Select sysdate into v_RUN_DATE from DUAL;

I am looking to do an insert into such as :

Execute Immediate 'Insert into A311300.HC_FND_STATS_NEVER_ANALYZED
(Run_Date,Owner,Object_Name,Index_Type,Collection_Date,INSTANCE,IS_INDEX)
(SELECT '''||to_date(v_Run_Date,'dd-mon-yyyy hh:mi:ss')||''',owner,index_name,index_type,sysdate,'''||v_DB_NAME||''',''1''
FROM dba_indexes@'||v_DB_NAME||
' WHERE last_analyzed is null
AND owner in (''IPBS'',''AR'',''FABS'',''BEN'',''HR'',''APPLSYS'',''SCRUBBER'',''HOB'')
AND temporary <> ''Y''
AND generated <> ''Y''
AND index_type NOT IN (''DOMAIN'',''LOB'')
AND table_name <> ''FND_LOBS'')';

The issue I am having is that the date being inserted (to_date(v_Run_Date,'dd-mon-yyyy hh:mi:ss')||)
into the date field is only inserted the date part, not the time.

What am I doing wrong??

printing dbms_output.put_line(to_char(v_Run_Date,'dd-mon-yyyy hh:mi:ss')); looks fine
printing dbms_output.put_line(to_date(v_Run_Date,'dd-mon-yyyy hh:mi:ss')); is only the date


I also just noticed that taking the variable out of the equation and just using sysdate has the same affect

(SELECT '''||to_date(sysdate,'dd-mon-yyyy hh:mi:ss' ||''',owner,index_name,index_type,sysdate,'''||v_DB_NAME||''',''1''




Thanks!

Edited by: 903261 on Dec 19, 2011 10:45 AM