date formats in SQL Assistant and Teradata Studio Express

Tools & Utilities
Enthusiast

date formats in SQL Assistant and Teradata Studio Express

Hi,

We have table columns defined as DATE FORMAT 'YYYY-MM'. If I run a query in SQL Assistant or Teradata Studio Express the date format defaults to what format is set in the preferences on all columns (SQL Assistant - Tools/Options/Data Format/Display dates in this format) or (Teradata Studio Express - Window/Preferences/Result Set Viewer Preferences/Display Date Format). I have even tried CAST to YYYY-MM but they still default to preferences settings.

I work with Teradata on a z/OS mainframe and If I use the TSO BTEQ or TSO ITEQ i see the dates in the correct format (how they defined in the table) 

without CAST.


Is there a setting somewhere in SQL Assistant or Teradata Studio Express to make them display the dates as they are defined and not as the default preference setting ?

 

Thanks,

Rick

 

 

  

  

6 REPLIES
Teradata Employee

Re: date formats in SQL Assistant and Teradata Studio Express

FORMAT phrases are only used when the database does the conversion to/from character strings. BTEQ queries use a special CLIv2 "field mode" which can be considered as implicitly casting to VARCHAR. Most tools retrieve numeric and date/time data in some "internal" format, in which case the tool is responsible for formatting the data for display to the user. If you want the database to apply the FORMAT instead, you will need to explicitly CAST the value returned to CHAR/VARCHAR.

Senior Apprentice

Re: date formats in SQL Assistant and Teradata Studio Express

Fred is correct that you effectively have to cast your date column to a character data type. The following is about the least amount of SQL that I've found to do what you want:

SEL TOP 5 
   ''||thedate as date_my_way
   ,thedate as date_from_db
FROM dbc.resusagespma;

In this system table column THEDATE has a format clause of 'yy/mm/dd'

HTH

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: date formats in SQL Assistant and Teradata Studio Express

Thanks for the responses, but being a long time Rexx guy I decided to write an interactive interface to BTEQ  ...which works very good on ISPF and I dont have to do all the CASTing ...it uses Rexx/Panels and ISPEXEC services ...I can run queries and commands SHOW table, HELP table, etc

The panel
)ATTR DEFAULT (@#")
 #  TYPE(TEXT)   INTENS(LOW)  SKIP(ON)
 _  TYPE(TEXT)                SKIP(ON)  COLOR(WHITE)
 @  TYPE(TEXT)   INTENS(HIGH) CAPS(OFF) COLOR(RED) SKIP(ON)
 [  TYPE(TEXT)   INTENS(LOW)  SKIP(OFF) COLOR(YELLOW) HILITE(REVERSE)
 |  TYPE(INPUT)  INTENS(HIGH) CAPS(OFF) COLOR(GREEN)  HILITE(USCORE)
 \  TYPE(OUTPUT) INTENS(HIGH) CAPS(ON)  COLOR(YELLOW) HILITE(USCORE)
)BODY CMD(ZCMD)
@Command ===>|ZCMD                                                            #
@ LPAR         =>\SYS #   @Teradata DB   =>\TSYS   @Date        =>\DAT        #
@ Userid       =>|USERID  @PRD/DRR       =>|SY_1/3 @Save SQL    =>|SQ_Y/N #
@ Report Width =>|WIDTH   @View SYSPRINT =>|SP_Y/N @Sub to Batch=>|SB_#
@ View DATAOUT =>|SO_Y/N# @View on WS    =>|SW_Y/N @Teradata RC =>\TRRC     #
@[|sql1                                                                     #
@[|sql2                                                                     #
@[|sql3                                                                     #
@[|sql4                                                                     #
@[|sql5                                                                     #
@[|sql6                                                                     #
@[|sql7                                                                     #
@[|sql8                                                                     #
@[|sql9                                                                     #
@[|sql10                                                                    #
@[|sql11                                                                    #
@[|sql12                                                                    #
@[|sql13                                                                    #
@[|sql14                                                                    #
@[|sql15                                                                    #
@[|sql16                                                                    #
@[|sql17                                                                    #
@[|sql18                                                                    #
@[|sql19                                                                    #
)INIT
VGET (USERID SY WIDTH SP SO SQ SW HISTC SB) PROFILE
VGET (SQL1 SQL2 SQL3 SQL4 SQL5 SQL6 SQL7 SQL8 SQL9) PROFILE
VGET (SQL10 SQL11 SQL12 SQL13 SQL14 SQL15 SQL16 SQL17) PROFILE
VGET (SQL18 SQL19) PROFILE
&USERID=&USERID
&SY=&SY
&WIDTH=&WIDTH
&SP=&SP
&SO=&SO
&SQ=&SQ
&SW=&SW
&SQL1=&SQL1
&SQL2=&SQL2
&SQL3=&SQL3
&SQL4=&SQL4
&SQL5=&SQL5
&SQL6=&SQL6
&SQL7=&SQL7
&SQL8=&SQL8
&SQL9=&SQL9
&SQL10=&SQL10
&SQL11=&SQL11
&SQL12=&SQL12
&SQL13=&SQL13
&SQL14=&SQL14
&SQL15=&SQL15
&SQL16=&SQL16
&SQL17=&SQL17
&SQL18=&SQL18
&SQL19=&SQL19
.HELP = BTEQIPH
.CURSOR=SQL1
)PROC
VER (&SY,LIST,1,3)
VER (&SQ,LIST,Y,N)
VER (&SP,LIST,Y,N)
VER (&SO,LIST,Y,N)
VER (&SW,LIST,Y,N)
VPUT (USERID SY WIDTH SP SO SQ SW HISTC SB) PROFILE
VPUT (SQL1 SQL2 SQL3 SQL4 SQL5 SQL6 SQL7 SQL8 SQL9) PROFILE
VPUT (SQL10 SQL11 SQL12 SQL13 SQL14 SQL15 SQL16 SQL17) PROFILE
VPUT (SQL18 SQL19) PROFILE
)HELP
FIELD(SYS)      PANEL(SYS)
FIELD(TSYS)     PANEL(TSYS)
FIELD(DAT)      PANEL(DAT)
FIELD(USERID)   PANEL(USERID)
FIELD(SY)       PANEL(SY)
FIELD(SQ)       PANEL(SQ)
FIELD(WIDTH)    PANEL(WIDTH)
FIELD(SP)       PANEL(SP)
FIELD(SO)       PANEL(SO)
FIELD(SB)       PANEL(SB)
FIELD(SW)       PANEL(SW)
FIELD(TRRC)     PANEL(TRRC)
FIELD(SQL1)     PANEL(SQL)
FIELD(SQL2)     PANEL(SQL)
FIELD(SQL3)     PANEL(SQL)
FIELD(SQL4)     PANEL(SQL)
FIELD(SQL5)     PANEL(SQL)
FIELD(SQL6)     PANEL(SQL)
FIELD(SQL7)     PANEL(SQL)
FIELD(SQL8)     PANEL(SQL)
FIELD(SQL9)     PANEL(SQL)
FIELD(SQL10)    PANEL(SQL)
FIELD(SQL11)    PANEL(SQL)
FIELD(SQL12)    PANEL(SQL)
FIELD(SQL13)    PANEL(SQL)
FIELD(SQL14)    PANEL(SQL)
FIELD(SQL15)    PANEL(SQL)
FIELD(SQL16)    PANEL(SQL)
FIELD(SQL17)    PANEL(SQL)
FIELD(SQL18)    PANEL(SQL)
FIELD(SQL19)    PANEL(SQL)
)END

 

The Rexx

/* REXX */
/*-------------------------------------------------------------------*/
/*                         REXX                                      */
/*          (Restructured EXtended eXecutor language)                */
/*-------------------------------------------------------------------*/
/* Program  Name...: BTEQI                                           */
/* Original Author.: Richard (Rick) Silvers                          */
/* Original Date...: 02-16-2017                                      */
/* Program  Purpose: Run BTEQ interactively                          */
/*-------------------------------------------------------------------*/
/* Maintenance Log.:                                                 */
/*-------------------------------------------------------------------*/
/* MM-DD-CCYY Programmer Comment                                     */
/* ---------- ---------- -------                                     */
/* 02-16-2017 R.Silvers  Created Program                             */
/*-------------------------------------------------------------------*/
/*                                                                   */
/*-------------------------------------------------------------------*/
/* Display the panel for input                                       */
/*-------------------------------------------------------------------*/
do forever
/*-------------------------------------------------------------------*/
/* Get System/date/Teradata system                                   */
/*-------------------------------------------------------------------*/
'ISPEXEC VGET (ZSYSID) SHARED'
'ISPEXEC VGET (SY ZPF17) PROFILE'
savePF17=ZPF17
ZPF17='VSQLHIST'
'ISPEXEC VPUT (ZPF17) PROFILE'
sys=zsysid
 
mm=substr(date(u),1,2)
dd=substr(date(u),4,2)
yy=substr(date(s),1,4)
dat=mm||'/'||dd||'/'||yy
 
if substr(sys,1,2) = '1S' & sy='1'
 then tsys='DEV'
else
if substr(sys,1,2) = '1S' & sy='2'
 then tsys='UAT'
else
if substr(sys,1,2) = '1X' & sy='1'
 then tsys='PRD'
else
if substr(sys,1,2) = '1X' & sy='3'
 then tsys='DRR'
"ISPEXEC DISPLAY PANEL(BTEQIP)"
select
   when rc    = 8                then do
ZPF17=savepf17
'ISPEXEC VPUT (ZPF17) PROFILE'
                      exit
                      end
otherwise call runsql
end
end
ZPF17=savepf17
'ISPEXEC VPUT (ZPF17) PROFILE'
exit
/*-------------------------------------------------------------------*/
runsql:
/*-------------------------------------------------------------------*/
/* Allocate the BTQMAIN application libraries                        */
/*-------------------------------------------------------------------*/
"ISPEXEC LIBDEF ISPLLIB DATASET ID('TERADATA.APPLOAD') STACK"
"ISPEXEC LIBDEF ISPLLIB DATASET ID('TERADATA.TRLOAD') STACK"
/*-------------------------------------------------------------------*/
/* If files don't exist create them otherwise alloc                  */
/*-------------------------------------------------------------------*/
 x = SYSDSN("'"userid()".SYSINB'")
if x \= 'OK'
   then do
"ALLOC DS('"userid()".SYSINB') LRECL(80) DSORG(PS) RECFM(F,B)",
"SPACE(10,10) CYLINDERS RELEASE NEW CAT"
        end
/*-------------------------------------------------------------------*/
 x = SYSDSN("'"userid()".SYSIN'")
if x \= 'OK'
   then do
"ALLOC DS('"userid()".SYSIN') LRECL(80) DSORG(PS) RECFM(F,B)",
"SPACE(10,10) CYLINDERS RELEASE NEW CAT"
        end
 
"ALLOC DS('"userid()".SYSIN') DD(SYSIN)",
"REUSE SHR"
/*-------------------------------------------------------------------*/
 x = SYSDSN("'"userid()".DATAOUT'")
if x \= 'OK'
   then do
"ALLOC DS('"userid()".DATAOUT') LRECL(5000) DSORG(PS) RECFM(F,B)",
"SPACE(20,20) CYLINDERS RELEASE NEW CAT"
        end
 
"ALLOC DS('"userid()".DATAOUT') DD(DATAOUT)",
"REUSE SHR"
/*-------------------------------------------------------------------*/
 x = SYSDSN("'"userid()".DATAOUTB'")
if x \= 'OK'
   then do
"ALLOC DS('"userid()".DATAOUTB') LRECL(5000) DSORG(PS) RECFM(F,B)",
"SPACE(20,20) CYLINDERS RELEASE NEW CAT"
        end
/*-------------------------------------------------------------------*/
 x = SYSDSN("'"userid()".SQLHIST'")
if x \= 'OK'
   then do
"ALLOC DS('"userid()".SQLHIST') LRECL(500) DSORG(PS) RECFM(F,B)",
"SPACE(50,50) CYLINDERS RELEASE NEW CAT"
        end
 
"ALLOC DS('"userid()".SQLHIST') DD(SQLHIST)",
"REUSE MOD"
/*-------------------------------------------------------------------*/
 x = SYSDSN("'"userid()".SYSPRINT'")
if x \= 'OK'
   then do
"ALLOC DS('"userid()".SYSPRINT') LRECL(133) DSORG(PS) RECFM(F,B,A)",
"SPACE(20,20) CYLINDERS RELEASE NEW CAT"
        end
 
"ALLOC DS('"userid()".SYSPRINT') DD(SYSPRINT)",
"REUSE SHR"
/*-------------------------------------------------------------------*/
/* Make a buffer for queueing the statements                         */
/*-------------------------------------------------------------------*/
userid=strip(userid,b,' ')
"MAKEBUF"
queue '.LOGON 'sy'/'userid',;'
queue '.SET NULL AS '''
queue '.SET RECORDMODE OFF;'
queue '.SET FORMAT OFF;'
queue '.SET PAGELENGTH 1000;'
queue '.SET WIDTH 'width';'
queue '.SET TITLEDASHES ON;'
queue '.SET SEPARATOR "|";'
queue '.EXPORT REPORT FILE=DATAOUT'
 
if sql1 \= ' '
   then queue sql1
if sql2 \= ' '
   then queue sql2
if sql3 \= ' '
   then queue sql3
if sql4 \= ' '
   then queue sql4
if sql5 \= ' '
   then queue sql5
if sql6 \= ' '
   then queue sql6
if sql7 \= ' '
   then queue sql7
if sql8 \= ' '
   then queue sql8
if sql9 \= ' '
   then queue sql9
if sql10 \= ' '
   then queue sql10
if sql11 \= ' '
   then queue sql11
if sql12 \= ' '
   then queue sql12
if sql13 \= ' '
   then queue sql13
if sql14 \= ' '
   then queue sql14
if sql15 \= ' '
   then queue sql15
if sql16 \= ' '
   then queue sql16
if sql17 \= ' '
   then queue sql17
if sql18 \= ' '
   then queue sql18
if sql19 \= ' '
   then queue sql19
        queue ';'
queue '.EXPORT RESET'
queue '.QUIT'
/*-------------------------------------------------------------------*/
/* Write the queue to SYSIN                                          */
/*-------------------------------------------------------------------*/
n=queued()
if sb='Y'
then do
"ALLOC DS('"userid()".SYSINB') DD(SYSINB)",
"REUSE OLD"
 SP='N'
 SO='N'
 SQ='N'
"EXECIO" n "DISKW SYSINB(FINIS"
"FREE DD(SYSINB)"
dummy=outtrap('tt.')
"SUBMIT 'MYUSERID.J1D(BTEQRBAT)'"
zedlmsg=tt.1
"ISPEXEC SETMSG MSG(ISRZ001)"
 return
    end
else do
sys=strip(sys,b,' ')
"EXECIO" n "DISKW SYSIN(FINIS"
"DROPBUF"
/*-------------------------------------------------------------------*/
/* Call BTQMAIN                                                      */
/*-------------------------------------------------------------------*/
address tso
"CALL 'TERADATA.APPLOAD(BTQMAIN)'"
trrc=rc
srrc=rc
/*-------------------------------------------------------------------*/
/* Display the SYSPRINT or REPORT File based on the users parm       */
/* Save sql history                                                  */
/*-------------------------------------------------------------------*/
 if SP = 'Y'
  then do
"ISPEXEC VIEW DATASET('"userid()".SYSPRINT')"
       end
 if SO = 'Y'
  then do
"ISPEXEC EDIT DATASET('"userid()".DATAOUT')"
       end
 if SQ = 'Y'
  then do
"MAKEBUF"
queue '+------------------------------------------------------+'
queue '+ LPAR        => 'sys
queue '+ Teradata DB => 'tsys
queue '+ Teradata RC => 'srrc
queue '+------------------------------------------------------+'
if sql1 \= ' '
   then queue sql1
if sql2 \= ' '
   then queue sql2
if sql3 \= ' '
   then queue sql3
if sql4 \= ' '
   then queue sql4
if sql5 \= ' '
   then queue sql5
if sql6 \= ' '
   then queue sql6
if sql7 \= ' '
   then queue sql7
if sql8 \= ' '
   then queue sql8
if sql9 \= ' '
   then queue sql9
if sql10 \= ' '
   then queue sql10
if sql11 \= ' '
   then queue sql11
if sql12 \= ' '
   then queue sql12
if sql13 \= ' '
   then queue sql13
if sql14 \= ' '
   then queue sql14
if sql15 \= ' '
   then queue sql15
if sql16 \= ' '
   then queue sql16
if sql17 \= ' '
   then queue sql17
if sql18 \= ' '
   then queue sql18
if sql19 \= ' '
   then queue sql19
        queue ';'
n=queued()
"EXECIO" n "DISKW SQLHIST(FINIS"
"DROPBUF"
  end
 if SW = 'Y'
  then do
/*-------------------------------------------------------------------*/
/* Check to see if the Workstation Connection is Established         */
/*-------------------------------------------------------------------*/
'ISPEXEC VGET ZWSCON'
if zwscon = ' '
   then do
zparm=' '
'ISPEXEC SELECT CMD(%WC &ZPARM)'
         end
/*-------------------------------------------------------------------*/
/*  Direct to C:\users\userid\downloads folder                       */
/*-------------------------------------------------------------------*/
"ISPEXEC VGET (ZSYSID) SHARED"
lpar=zsysid
dsn="'"userid()".DATAOUT'"
dsnx=strip(dsn,b,"'")
 
pcf='C:\Users\'||userid()||'\downloads\'||dsnx||'_from_'lpar||,
'_'||date(s)||'.TXT'
 
'ISPEXEC FILEXFER HOST(DSN) WS(PCF) TO(WS) TEXT'
 
'ISPEXEC SELECT WSCMD(CMD /C START NOTEPAD &PCF) INVIS'
  end
  end
"ISPEXEC LIBDEF ISPLLIB"
"FREE DD(SYSPRINT SYSIN DATAOUT SQLHIST)"
return

Enthusiast

Re: date formats in SQL Assistant and Teradata Studio Express

Hi Dave,

 

Could you please explain how by prefixing ''|| the date format changes in the below query

 

SEL TOP 5
   ''||thedate as date_my_way
   ,thedate as date_from_db
FROM dbc.resusagespma;

Senior Apprentice

Re: date formats in SQL Assistant and Teradata Studio Express

Hi,

 

  1. The || symbol is for concatenation.
  2. Concatenation only works with character data. Any data that is not character (e.g. a date column) must be converted before the concatenation is done. In Teradata terms this is 'formatting'.
  3. Formatting rules are drawn using a hierarchy. Teradata looks for a FORMAT clause in the following locations (in sequence): your sql, the top level view being accessed, lower level views (in sequence), the table, the system default format for the data type.
  4. In this example the FORMAT clause from the table definition is used (FORMAT 'YYYY-MM'). Hence the displayed result.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: date formats in SQL Assistant and Teradata Studio Express

Hey Dave I tried that and it works great ...Thanks, Rick