Re: Return minutes as date datatype

Thanks Mark

I'm glad you understood what I meant!

I can perhaps get by with the time prefixed with a date if the users are warned to ignore the date element (or I can somehow format it out in Access). For this approach to work I need the date part of all the returned rows to be the same (I'm only interested in the time). Before I try this approach I would like to know why:

*To_Date(To_Char(DateField,'HH24:MI'), 'HH24:MI') : puts '01/07/2004' in front of the hour/minute part (the original data is not for '01/07/2004' (and is not the date on which I was running the expression), but this is the date given in the all rows resulting from the above expression).

I would have expected the date element to show either today's date (as the date is not specified in the To_Date expression) or 1st Jan 1900 (or whatever the base date is in Oracle if it differs from the Windows 'standard').

Thanks

Paul

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:Io6dnXP3TbhvN2LdRVn-oQ_at_comcast.com...
>
> "Joel Garry" <joel-garry_at_home.com> wrote in message
> news:91884734.0407211626.83eb581_at_posting.google.com...
> | "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
> news:<G-CdnTYoOqwZL2PdRVn-ug_at_comcast.com>...
> | > paul, dates is dates -- if you have a date datatype, it will always
have
> a
> | > date and a time element
> | >
> | > so, there is no way to return a date datatype that does not include a
> date
> | > element
> | >
> | > your best shot is to revamp your generic tool to handle time values
> | > (returned as number, but perhaps returned as INTERVAL DAY if Access
ADO
> can
> | > handle that)
> | >
> |
> | Am I missing something?
> |
> | 1* select To_Char(sysdate,'HH24MI') from dual
> | SQL> /
> |
> | TO_C
> | ----
> | 1725
> |
> | Seems numeric enough to me?
> |
>
> yes, you are missing that the OP wants the value returned as a DATE
datatype
> with no date element -- your expression returns a character datatype,
which
> his generic interface is handling with comparison operators that are not
> appropriate to what he wants to accomplish
>
> SQL> select
> 2 dump(sysdate)
> 3 ,dump(to_char(sysdate,'hh24mi'))
> 4 ,dump(to_number(to_char(sysdate,'hh24mi')))
> 5 from dual
> 6 /
>
> DUMP(SYSDATE)
> -------------------------------------------------
> DUMP(TO_CHAR(SYSDATE,'HH24MI'))
> -------------------------------------------------
> DUMP(TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI')))
> -------------------------------------------------
> Typ=13 Len=8: 212,7,7,22,7,51,34,0
> Typ=1 Len=4: 48,55,53,49
> Typ=2 Len=3: 194,8,52
>
> ++ mcs
>
>
Received on Thu Jul 22 2004 - 10:45:32 CDT