Getting export results in same date format as in Teradata table using BTEQ

Tools

Getting export results in same date format as in Teradata table using BTEQ

I am fairly new to Teradata. I have been struggling to get exact format for all the dates in my select query when doing an export using BTEQ. 

Here is my script :

/opt/teradata/client/15.10/bin/bteq <<EOI

.LOGON hostname/username,password;

.EXPORT REPORT  FILE = sample_table.csv

.SET RECORDMODE OFF

.SET TITLEDASHES OFF

.SET FORMAT OFF

.SET WIDTH 65531

.SET SEPARATOR ","

SELECT  top 100 * from table;

.EXPORT RESET

.LOGOFF;

.EXIT;

EOI

 

The result gets populated in sample_table.csv but all the date formats are in yy-MM-dd format where as the requirement is to get them exactly in the same format as they are in the Teradata table. 

 

 

 

Thanks

Tags (1)
3 REPLIES
Teradata Employee

Re: Getting export results in same date format as in Teradata table using BTEQ

"Exactly the same format as they are in the Teradata table" would be an internal binary representation; probably not what you really want.

BTEQ in FIELDMODE will use the FORMAT associated with the column to convert to character form. Other tools (e.g. SQL Assistant) typically convert the ODBC/JDBC/etc. driver format to the client's local form. You could ALTER the column format in the table, or explicitly CAST the date field(s) to some other format, or possibly SET SESSION DATEFORM=ANSIDATE; if what you want is yyyy-mm-dd format.

Re: Getting export results in same date format as in Teradata table using BTEQ


I tried using SET SESSION DATEFORM=ANSIDATE; to get all date fields in YYYY-mm-dd format but the end result is always yy-MM-dd :(   Is there anything wrong with my shell script?/opt/teradata/client/15.10/bin/bteq <<EOI

.LOGON hostname/username,password;

.EXPORT REPORT  FILE = sample_table.csv

.SET RECORDMODE OFF

.SET TITLEDASHES OFF

SET SESSION DATEFORM=ANSIDATE;

.SET FORMAT OFF

.SET WIDTH 65531

.SET SEPARATOR ","

SELECT  top 100 * from table;

.EXPORT RESET

.LOGOFF;

.EXIT;

EOI

Teradata Employee

Re: Getting export results in same date format as in Teradata table using BTEQ

Please provide the SHOW TABLE. Need to see the data type and format for the column that contains the date.