All Forums Tools
taruntrehan 43 posts Joined 10/12
28 Mar 2014
BTEQ Import Invalid Date Issue

Hi All,
I am trying to port data from a flat file to TD via BTEQ.
The table definition is :

CREATE MULTISET TABLE _module_execution_log 
      system_id INTEGER,
      process_id INTEGER,
      module_id INTEGER,
      julian_dt INTEGER,
      referral_dt DATE FORMAT 'YYYY-MM-DD',
      start_dt_tm TIMESTAMP(6),
      end_dt_tm TIMESTAMP(6),
      ref_s_cnt INTEGER,
      ref_d_cnt INTEGER)
PRIMARY INDEX ( module_id );

Following are 2 sample records that i am trying to load in the table :

1|1|30|2007073|Mar 14 2007 12:00:00:000AM|Mar 15 2007  1:27:00:000PM|Mar 15 2007  1:41:08:686PM|0|0
1|1|26|2007073|Mar 14 2007 12:00:00:000AM|Mar 15 2007  1:27:00:000PM|Mar 15 2007  1:59:40:620PM|0|0

Snippet for my BTEQ script :

	(   system_id INTEGER
    	,process_id INTEGER
    	,module_id INTEGER
    	,julian_dt INTEGER
    	,referral_dt DATE FORMAT 'YYYY-MM-DD'
    	,start_dt_tm TIMESTAMP
    	,end_dt_tm TIMESTAMP
    	,ref_s_cnt INTEGER
    	,ref_d_cnt INTEGER

	INSERT INTO _module_execution_log
    ( 	system_id

I get the following error during import :

 *** Failure 2665 Invalid date.
                Statement# 1, Info =5

 *** Failure 2665 Invalid date.
                Statement# 1, Info =5

I tried the following to avoid the error :

cast(cast(substr(:referral_dt,1,11) as date format 'MMMBDDBYYYY') as date format 'YYYY-MM-DD')

However, it is still failing with the same error.
Appreciate any inputs to accomplish this task.

Regards, Tarun Trehan
Santanu84 122 posts Joined 04/13
31 Mar 2014

Hi Tarun
Try using below.
SEL  CAST(CAST( SUBSTR('Mar 14 2007 12:00:00:000AM', 1, 11) AS VARCHAR(11)) AS DATE FORMAT'MMMBDDBYYYY') ;
But still using so many casting may not a good solution. See if the column can be changed to TIMESTAMP like other columns present in the table.
Thanking You

taruntrehan 43 posts Joined 10/12
09 Apr 2014

Thanks for the comments.
I considered the following approach :
1. Get all data exported from source table to a play table where all columns are in varchar format.
2. Then transform the required columns when moving from play table to target.
i used TO_TIMESTAMP function present in TD 14.
Worked like a charm, :)

Tarun Trehan

pathanjali 4 posts Joined 12/14
01 Jun 2016
sel cast(cast('Mar 14 2007 12:00:00:000AM' as char(11)) as date format 'MMMbDDbYYYY')

you can use the above syntax to convert it into understandable format. though this hinders the performance.

"Great minds discuss ideas; average minds discuss events; small minds discuss people"

You must sign in to leave a comment.