How to cast the timestamp?

UDA
Enthusiast

How to cast the timestamp?

Hi All,

I have a column in the table with timestamp(0) data type. My requirement is to extract the time portion of this column and compare if it is between 00:00:00 to 04:59:00 i.e upto morning 5 am.

create table entry
(
start_tmstp timestamp(0) format 'yyyy-mm-dd hh:mis:ss',
other columns...
)

select * from entry
where start_tmstp between 00:00:00 to 04:59:59

Can you please help me in completing the above SQL? Thanks in advance.

Regards
Rahul

3 REPLIES
Enthusiast

Re: How to cast the timestamp?

Sorry, I should have posted this in Database section. I will post it there.
Enthusiast

Re: How to cast the timestamp?

Hi,
Try out this:

select * from entry
where extract(hour from start_tmstp)<5 and extract(minutes from start_tmstp)<60
and extract(seconds from start_tmstp)<60

Junior Contributor

Re: How to cast the timestamp?

Hi Rahul,

WHERE CAST(start_tmstp AS TIME(0)) BETWEEN TIME '00:00:00' AND TIME '04:59:59'

Dieter