truncating while passing timestamp value to bteq script

Third Party Software

truncating while passing timestamp value to bteq script

Hi all ,

i justed started working in teradata (begginer)


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.


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

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


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?

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.


Re: truncating while passing timestamp value to bteq script

Thanks for the help regarding issue1 FredSmiley Very Happy


issue 2:

example code:






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