truncating while passing timestamp value to bteq script

Third Party Software
Highlighted
Enthusiast

truncating while passing timestamp value to bteq script

Hi all ,

i justed started working in teradata (begginer)

ISSUE 1:

i have a column  called "timestamp" from table "tableA" ,which is in timestamp(0) format while exporting from bteq script in into a data format it is changing its formats.

example:

inside table value: "8/3/2019 10:42:43"

after exporting value to textfile it becomes "2019-08-03 10:42:43"

ISSUE 2:

while using the exported value in a bteq script "its trimming till date" .

example : in bteq scrip i have used a var called last_time_stamp , so value becomes "2019-08-03" .

i am getting the following error

**Failure 5407 Invalid Operation For DateTime Or Interval format

 

can anyone suggest how to export in correct format and use it in bteq scripts? ..any help is appreciated?

2 REPLIES 2
Teradata Employee

Re: truncating while passing timestamp value to bteq script

Issue 1: It's sort of the other way around. "Inside the table" the value is non-printable binary. The external printable format 'yyyy-mm-dd hh:mi:ss' is "correct" in the sense that it is standard. Whatever other client you are using to query is reformatting as 'm/d/yyyy hh:mi:ss' is the non-standard one. Teradata does not support single-digit month or year formats. You could use explicit formatting with CAST or TO_CHAR to return a character string in the format 'mm/dd/yyyy hh:mi:ss' if you prefer (and could even remove leading zeros from that string using something like REGEXP_REPLACE). 

 

Issue 2: You would need to show an example of your script and what is happening. BTEQ does not support script variables (though it does allow host variables / USING in SQL when .IMPORT is in effect) so the "trimming" may be a command shell coding issue rather than BTEQ.

Enthusiast

Re: truncating while passing timestamp value to bteq script

Thanks for the help regarding issue1 FredSmiley Very Happy

 

issue 2:

example code:

Select 

col1,

col2,

col3,

db_load_stmt

from srcdb.tblname where db_load_stmt> last_sucess_tmstp;

we are passing ,srcdb,tblname and "last_sucess_tmstp" as commandline arg. while passing the value "2019-08-03 10:42:43" for last_sucess_tmstp .i was getting "2019-08-03" there was a blank space while exporting "db_load_stmt" from dbtable.

 

bteq was accepting as a another cmd line arg..now my issues are resolved.

want to learn more about teradata ,can u help me with where can i find tutorials for absolute beginners

thanks for the help 

Chetan Smiley Wink