All Forums Database
gander_ss 74 posts Joined 02/07
16 Jan 2008
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

vineet 7 posts Joined 01/08
16 Jan 2008

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.....

gander_ss 74 posts Joined 02/07
16 Jan 2008

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

nithyanandam 65 posts Joined 10/04
16 Jan 2008

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

gander_ss 74 posts Joined 02/07
21 Jan 2008

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

famalau 43 posts Joined 08/07
21 Jan 2008

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.

Best regards,

Fabio

lateshpant 16 posts Joined 01/08
22 Jan 2008

alternativel you may cast timestamp as time.. as shown belowSELECT CURRENT_TIMESTAMP(0) ( TIME (0))SELECT CURRENT_TIMESTAMP(6) ( TIME (6))

gander_ss 74 posts Joined 02/07
23 Jan 2008

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

You must sign in to leave a comment.