Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 14 Jan 2008 @ 18:15:23 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Convert time from twenty four hour to twelve hour format
From:   Michael Larkins

Hello Smita:

When doing a CAST, you can only convert the data to a data type, not a format. Therefore, you wrote your CAST incorrectly:

     > > Sel cast(cast(HALF_HR_STRT_TM as format 'HH:MI:SSBT') as varchar(10)) from table

should be either: cast((half_hr_strt_tm (format 'hh:mi:ssbt')) as char(11)) or half_hr_strt_tm (format 'hh:mi:ssbt')(char(11))

Remember: format is an attribute, not a data type. CAST expects a data type. Additionally, when you have data that is a fixed length, do not use VARCHAR because it will add a 2 byte length indicator at the front of the data to know the length. Therefore, this data would all by stored in SPOOL as 2+11 = 13 bytes for each formatted date instead of only 11 bytes.

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020