CODEWITHSUNDEEP
ms-access
angel
angel

Reputation: 4632

how to get a time between 2 columns of time (access)

select mydate as [date],enter as [Enters], left as [lefts],   CAST (  CAST (  DATEDIFF (hour, enter, left) as varchar)+':'+ 
CAST (  datediff (MINUTE,enter,left)%60 as varchar)
as varchar)as [time realizated] from timer where user='xxxx'

this was my query it worked in sql server but i must to change the database to access now it doesn't work with this i got a history complete of a person

for example

date       enters   lefts  time realizated
29/06/2011   8:00   9:30   1:30

Upvotes: 1

Views: 69

Answers (2)

Saic Siquot
Saic Siquot

Reputation: 6513

select mydate as [date],enter as [Enters], left as [lefts],

   int((left-enter)*24) & ":" & int((left-enter)*1440) mod 60  as [time realizated] 

from timer where user='xxxx'

please check about &(concatenate), and int() because I don't remember if this are the write. but I used a lot of this date-matematic on access far time ago

so here int() means floor()
you will need also to pad with ceros

Upvotes: 1

JDunkerley
JDunkerley

Reputation: 12505

The DateDiff syntax is different in Access. I think:

DateDiff('n',[Enters],[Lefts])

Should get you the time in minutes.

If you want it as a Time you could do:

DateAdd('n',DateDiff('n',[Enters],[Lefts]),#00:00:00#)

Upvotes: 1

Related Questions