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 :

USING 
	(   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
     	,process_id
     	,module_id
     	,julian_dt
     	,referral_dt
     	,start_dt_tm
     	,end_dt_tm
     	,ref_s_cnt
     	,ref_d_cnt
    )
	VALUES ( 
		:system_id
    	,:process_id
    	,:module_id
    	,:julian_dt
    	,:referral_dt
    	,:start_dt_tm
    	,:end_dt_tm
    	,:ref_s_cnt
    	,:ref_d_cnt);

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.
Thanks.
 

Regards, Tarun Trehan http://allzhere.in
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
Santanu 

taruntrehan 43 posts Joined 10/12
09 Apr 2014

Hi,
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, :)

Regards,
Tarun Trehan
http://allzhere.in

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.
 
 

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

You must sign in to leave a comment.