Return minutes as date datatype

Hi,

I need to construct an sql statement that will give me just the hour/minute portion of a time *as a date*.

I have a generic report screen (in Access linked to Oracle via ADO, all the SQL statements are passthrough i.e. in Oracle syntax) that allows the user to select a field from a view, then select an expression and finally select a value.

The list of expressions is determined by checking the ado data type of the field. If the field is numeric or date, then < and > are options. If the data type is text, then they are not (but In, Like, etc are).

I can return the hour/minute portion of the date easily enough using TO_CHAR, but this returns the data in text format which is no good for my generic report screen.

I handle dates by using Trunc(DateField) - I don't mind if this is being returned as a numeric or a date, just so long as it is not text.

I have tried:
* DateField-Trunc(DateField) : This just converts to numeric - I need to see
hour:minute
* To_Char(DateField,'HH24:MI') : But this returns a character

• To_Date(To_Char(DateField,'HH24:MI'), 'HH24:MI') : But this puts '01/07/2004' in front for the hour/minute part I want

I am not overly familiar with Oracle syntax, so hopefully I am just missing some kind of date format command, but I'm having a hell of a time trying to find it.

Thanks

Paul Received on Wed Jul 21 2004 - 09:22:38 CDT