BTEQ Import Invalid Date Issue

Tools & Utilities
Enthusiast

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.

3 REPLIES
Enthusiast

Re: BTEQ Import Invalid Date Issue

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 

Enthusiast

Re: BTEQ Import Invalid Date Issue

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

Re: BTEQ Import Invalid Date Issue

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.