FYI: date parameters w/ ADO & Oracle

Hi I just thought that since this article had helped me in the past it was worthwhile my updating it.

Changing the type of the parameter from adDate to adDBTime worked like a charm many months ago. However I just ran into a problem where the I my output date parameter was getting the time of the returned date/time but the date was always today.

I changed our code so that where we were changing adDate to adDBTime we now changed it to adDBTimeStamp and that fixed the problem.

I had wondered about using adDBTime when we had done it since the docs stated that it only represented the time, but since it worked, I wasn't complaining.

I think what happened is that we changed from using MDAC 2.1 to using MDAC 2.5, and they must have fixed the behaviour between those 2 versions. (I haven't however reinstalled 2.1 in order to check this hypothesis :-)

Mike

• OLD NEWSGROUP MESSAGE FOLLOWS -------------------- Thank you Thomas.

 I notice that you post several very useful replies on the newsgroups - thanks for those as well. After a sleep I had come to  the same
 conclusion that you specified below. In fact the problem was one with VB (dare I mention it!).

 If people are returning parameters to VB using ado then make sure the parameter type is adDBTime rather than adDate.  The second strips off the time component.

 Sascha

 In article <HigCODDncuj9NvHFsjfl0LrHHCvm_at_4ax.com>,    tkyte_at_us.oracle.com wrote:
> A copy of this was sent to saschac_at_my-deja.com
> (if that email address didn't require changing)
> On Mon, 11 Oct 1999 17:22:38 GMT, you wrote:
>
> >Hello all, I am trying to get out the time part of a date field. I
 know
> >I can do such a thing like [select to_char(lastmodifiedon,'dd-mon-yy
> >hh24:mi:ss') from tblobject where id=7957] and that works but I need
 to
> >be able to do this in a stored procedure.
> >
> >here is a fragment of my pl/sql procedure
> >
> >It works fine if i remove the to_char part but i need the time
> >
> >dLastModifiedOn out date
> >vtblObject tblObject%ROWTYPE;
> >select * into vtblObject from tblObject where id=idxx;
> >dLastModifiedOn:=to_char(vtblObject.LastModifiedOn,'dd-mmm-yyyy
 hh24:mi:
> >
> >Any suggestions please. I would like the dLastModifiedOn to stay as
 a
> >date.
>
> Just assign:
>
> dLastModifiedOn := vtblObject.LastModifiedOn;
>
> thats it -- the dLastModifiedOn, being a date, will get the DATE and
 TIME
> component. Do not to_char the date to assign to another date. Only
 use to_char
> when you are getting ready to display a date somewhere (to_char is
 for
> formatting -- the date will keep all of the date/time pieces for you)
>
> >
> >Many thanks to you all
> >
> >Sascha Curzon
> >Victoria & Albert Museum
> >London
> >England
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
 Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated
 June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
 Corporation
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Sep 06 2000 - 16:30:11 CDT