[AccessD] SQL datetime to Access date conversion SOLVED - sloppy math.

Hi Doug

Explicit types are good.
You could sport these here as well but others may claim it to be overkill:

  datTime = TimeValue(Format(myDateTime, "hh:nn:ss"))

or (a favourite of Drew):

  datTime = TimeSerial(Hour(myDateTime), Minute(myDateTime), Second(myDateTime))

/gustav


>>> dbdoug at gmail.com 01-09-2009 17:07 >>>
Why this reroute?  Um, ignorance??  For some reason I had it stuck in my
head that there was a 'TimePart' function, and when I couldn't find it, I
used the subtraction.  I also didn't realize I could subtract dates directly
like that.  Blame that on my current attempts to learn C# - I've been burned
so many times by not doing explicit type conversions on everything.

Thanks again!

Doug

On Tue, Sep 1, 2009 at 7:57 AM, Gustav Brock <Gustav at cactus.dk> wrote:

> Hi Doug
>
> Well, that explains.
> But why this reroute? The time part can be obtained directly:
>
>  datTime = TimeValue(myDateTime)
>
> or, in a query with native SQL functions:
>
>  TimePart = myDateTime - Int(myDateTime)
>
> However, that will return a numeric value.
> If you must have a datetime value you can use CDate:
>
>  TimePart = CDate(myDateTime - Int(myDateTime))
>
> But then TimeValue will be easier to use.
>
> /gustav
>
>
> >>> dbdoug at gmail.com 01-09-2009 16:36 >>>
> Thanks to all who replied, and apologies for not writing a more detailed
> question.  It was me - I was splitting the time out of the datetime field
> using the following formula to get the fraction after the decimal point:
>
> Timepart = CSng(myDateTime)-CInt(CSng(myDateTime))
>
> as soon as I changed that to use CDbl instead of CSng, it started working.
> I'm surprised at the difference (I need to do some research on this).  As an
> example, 1:00:00 PM using CSng comes out as .54295875 and using CDbl comes
> out as .5416666667.  That's a difference of .00129208 of a day or
> approximately 1.85 minutes.
>
> Doug Steele