Archives of the TeradataForum

Message Posted: Fri, 04 Aug 2006 @ 17:20:35 GMT

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

PryorCh:

It appears that you are new to Teradata and formatting. The order of operations and your CAST are not correct. You cannot format character data using 'yyyy', 'mm' and 'dd'. Character data can only be 'x' so if you wish to use the former you have to make the data a date FIRST. The following sel shows the sequence to take a character "date" and make it a date. Then, it can be concatenated with a character string the represents hours, minutes and seconds so that a proper character string can be converted to a timestamp. Timing is everything, a step at a time.

     sel ((((('06/10/01'(date))))(format 'yyyy-mm-dd'))||'10:10:10')(timestamp)

So you will need to do something like the following:

     sel ((substring(chardate from 1 for 8) (date))(format
     'yyyy-mm-dd'))||substring(chardate from 9 for 9)(timestamp)
      from cdatetbl

or

     sel cast(((substring(chardate from 1 for 8) (date))(format
     'yyyy-mm-dd'))||substring(chardate from 9 for 9) as timestamp)
      from cdatetbl

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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