Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Return minutes as date datatype
Paul wrote:
> 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
Store as a DATE data type and return based on TO_CHAR.
Daniel Morgan Received on Thu Jul 22 2004 - 00:12:29 CDT