timestamp 1

[simonkatz]

How to extract date and time from the teradata timestamp?

 

[rrrkrishnan]

select substr(cast(timestamp_col as char(19)),12,8) as time_only from tablename;
select cast(timestamp_col as date) as date_only from tablename;

 

[dnoeth]

Casting to Time is easy, you just can't get less precision:
select cast(current_timestamp as time); works
select cast(current_timestamp(2) as time(0)); doesn't

Dieter

 

[rrrkrishnan]

We have V2R5 and when I submit this select,
"select cast(timestamp'2003-12-31 09:00:00' as time)"
I get the error message
5407:Invalid operation on an Ansi datetime or Interval Value.
Please let me know if I am doing anything wrong.

 

[dnoeth]

Did you run it from QueryMan?
Uncheck "Allow use of ODBC SQL Extensions in queries" in Tools->Options->Query.
But you'll need that option sometimes (e.g. to create a stored procedure, so the better way is to configure the ODBC datasource:
Options->DateTime Format to 'AAA'

Dieter