[solved]time from date-time and from string

[BIsrik]

Hi is i want to extract time part from the date-time datatype in RS and also convert a string like '08:00' to time. How will i do it...

Actually i want to compare the extracted time part of a date-time column with a time converted from a string as shown above..

the expression would be like this...

Ctime(date-time) = Ctime('08:00')

Any help for this..

Srik

[sir_jeroen]

what's the underlying database?

With Oracle it's:
TO_CHAR(SYSDATE, 'HH:MM:SS') AS TimeAsText

[BIsrik]

its oracle. Then what shall i use for converting a string like 08:00 to time. Basically i want to do similar to this one..Ctime(Datetime column)=Ctime('08:00')

Srik

[sir_jeroen]

Have you tried: TO_DATE(TO_CHAR(SYSDATE, 'HH:MM:SS'),'HH:MM:SS' )?

Look in:


http://www.techonthenet.com/oracle/functions/

Could you plz... mark this topic as closed when it's been solved

[BIsrik]

yeah i have tried that b4. During to_date function RN gives an error saying there is no valid month..

Srik

[Draoued]

Try with MI instead of MM.

so TO_CHAR(SYSDATE, 'HH24:MI')

[BIsrik]

extacting time from date-time is achieved. But how to convert it to a time format..I am not getting an alternative for that...help me on that...

Srik

[Draoued]

In order to be sure I understand properly your need.
You have some kind of prompt where User can type in a time
and then filter based on that .


Have you tried this :
TO_CHAR(SYSDATE, 'HH24MI')  = #prompt("Time")#

Then for the TIME prompt you put a Format or a mask , which displays it with the ":" in the middle, but don't use it for the filtering.
Then you are filtering on a String and not Time field.

select TO_CHAR(SYSDATE, 'HH24MI') from dual
This returns   1457 as string.

[BIsrik]

ok you can consider that way also. Actually there is no prompt for time. There is a field of date-time datatype. I need to extract the time from that and compare with a time [which is constant value]. I can't do the comparison on the charecters as they might tend to problem.

For this reason i want to convert the string to a time field. we have function Ctime which handles that.. How should that logic be applied in the RN...

Srik

[BIsrik]

Ok i got the way of achieving this...I first took the date part from the date-time, concated with the constant string and converted it back to the date-time. i then added the filters..

Thanks for all ur suggestions.

Srik