time from timestamp

Database
Enthusiast

time from timestamp

Hi All,

I need to extract time portion from timestamp(0).
I tried this SQL.

select Cast(col_name As time(0)) From table_name;

But I am getting folloeing error:

*************************************************************
5407 : Invalid operation on an ANSI date/time or interval value.
*************************************************************
Any Idea.

Regards,
gander_ss
7 REPLIES
Enthusiast

Re: time from timestamp

hi gander_ss,

I am not sure about this error but once i got the same error in my case the problem was I have defined col_name as date type but for using cast i think it should be of TIMESTAMP type.

Hope it works for you.....

Enthusiast

Re: time from timestamp

Hi Vineet,

In my case col is TIMPSTAMP(0).
And I need to extract time portion not the date.
By the way plz. give me that syntax.

Regards,
gander_ss
Enthusiast

Re: time from timestamp

You can try something like this:

SELECT cast(cast(CAST(CURRENT_TIMESTAMP AS char(19)) as TIMESTAMP(0)) as time(0));

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

Current TimeStamp(6)
--------------------
10:25:18
Enthusiast

Re: time from timestamp

Hi Nityanand,

I have tried this SQL as well.
But getting the same error.

But able to run this SQl .

Sel Cast(Substr(Cast( CURRENT_TIMESTAMP As varchar(20)),12,19) As Interval Hour(2) To Second(0))

Any idea.

Regards,
gander_ss
Enthusiast

Re: time from timestamp

Hi gander_ss,

Try the following and see if it helps you:

SELECT CAST((CAST(current_timestamp(0) AS TIME(0) WITH TIME ZONE)) AS CHAR(8));

Cheers.
Teradata Employee

Re: time from timestamp

alternativel you may cast timestamp as time.. as shown below

SELECT CURRENT_TIMESTAMP(0) ( TIME (0))
SELECT CURRENT_TIMESTAMP(6) ( TIME (6))
Enthusiast

Re: time from timestamp

Hi Guys,

I am able to do my task using following SQL :

select Cast(Substr(Cast(Time_stamp As varchar(20)),12,19) As time(6))

Thanks for suggestion.

Regards,
gander_ss