Date and format

Analytics

Date and format

Hi,

This is the o/p of the show table salary :

CREATE MULTISET TABLE FINANCIAL.salary ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      FIRST_NAME VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,

      LAST_NAME VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,

      ID INTEGER,

      GRADE CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,

      DOB DATE FORMAT 'YY/MM/DD',

      STATE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,

      SALARY INTEGER)

PRIMARY INDEX ( ID );

This is the o/p of the sel * from salary








FIRST_NAME LAST_NAME ID GRADE DOB STATE SALARY
Peter Wu 1012 I            4/20/1984 VA 4000
? ? ? ? ? ? ?
Nell   1029 IV           8/20/1981 VA 1000
AJIT SINGH 1039 III          11/11/1985 TX 2000
Jason Pruss 1023 II           1/19/1965 GA 1000
Mike Craig 1033 I            11/26/1975 VA 5000
null Lee 1082 ? 8/21/1980 ? 1000
Lisa Kelly 1002 I            5/21/1985 CT 3000
MONA ? 1080 III          3/1/1984 CA 2000
Ja%k Ho 1000 III          1/1/1988 CT 4000
J%%L SHU 1099 IV           1/1/1967 TX 1000
Keith Kelly 1001 II           1/14/1981 CT 2000
Jack Chen 1066 IV           5/15/1988 CT 1000
AJIT ? 1020 II           2/1/1984 C_ 3000
Ania Howell 1045 III          10/15/1985 TX 1000
Deb ? 1081 II           12/1/1989 ? 4000

I agree the way the dates are stored in Tables is different, howvever I have meantioned the format as 'YY/mm/dd' so why is the DOb not being pulled as 84/04/20 and likewise for all the records?

3 REPLIES
Enthusiast

Re: Date and format

Are you running this query using teradata sql assistant ? sometime the answerset of it has default format. If you want to see the output in YY/mm/dd then I would say you should use CAST function in your select query. 

Enthusiast

Re: Date and format

Use bteq to run sel * from table and you will get in format mentioned in create table syntax.

Harpreet

Enthusiast

Re: Date and format

Hi,

If you are using SQL assistant then you can change the way the dates are displayed by chosing the appropriate format in the tools menu

tools -> option -> general tab

Regards

Rajeev