All Forums Tools
17 Aug 2016
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:
Fred 1096 posts Joined 08/04
17 Aug 2016

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

18 Aug 2016

 

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

ToddAWalter 316 posts Joined 10/11
18 Aug 2016

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

You must sign in to leave a comment.