Link to home
Start Free TrialLog in
Avatar of asans
asans

asked on

Date/Time

Given date 1, time 1 and date 2, time 2, how do i find the difference in minutes. Foe example, the total time between 15/05/2005, 5:25pm  and 16/05/2005, 7:15pm.    
Avatar of [ fanpages ]
[ fanpages ]

Hi,

In your SQL query statement, use the DateDiff() function.

If your Date1 & Time1, and Date2 & Time2 columns are separate [i.e 4 different columns] use:

SELECT DateDiff("n", Date1 + Time1, Date2 + Time2) FROM Your_Table

Otherwise, if Date1 contains a date & a time, and Date2 contains a date & a time, use:

SELECT DateDiff("n", Date1, Date2) FROM Your_Table

BFN,

fp.
Hi asans,

Try this...

Private Sub Command1_Click()
    Dim Date1 As Date, Date2 As Date
    Date1 = "13/5/2005 12:41 PM"
    Date2 = "12/5/2005 12:41 PM"
    MsgBox Round((Date1 - Date2) * 24 * 60)
End Sub
   


Bye
---
Harish
asans,
    In VB each day is counted as one. But in one day, you will have 24*60 minutes. So you should multiply the difference with 24*60 or 1440

Hope this helps :)
Or using DateDiff() without SQL:

Private Sub Command1_Click()
    Dim DateA As Date, DateB As Date
    Dim mins As Long
    DateA = "5/15/2005 5:25 PM"
    DateB = "5/16/2005 7:15 PM"
    mins = DateDiff("n", DateA, DateB)
    MsgBox "mins = " & mins
End Sub
> Or using DateDiff() without SQL:

Yes, it makes a lot more sense to use a built in feature of the language than the odd kludge that mgh_mgharish posted.
Avatar of asans

ASKER

i forgot to mention, i have 4 textboxes for date 1, time 1, date 2 and time 2. I see something like
DateA = "5/15/2005 5:25 PM"
DateB = "5/16/2005 7:15 PM"
How do i link that to my 4 textboxes? In other words one textbox capture the date only and the other time. but from the above, the DateA, say, has both date and time. How do i link the 2 to have one variable like DateA.
ASKER CERTIFIED SOLUTION
Avatar of veeru_friend
veeru_friend

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of asans

ASKER

Now i want to use the date pickers.
If i try to use this

Private Sub Command3_Click()
Dim DateA As Date, DateB As Date

DateA = CDate(DTPicker1 & " " & DTPicker3)
DateB = CDate(DTPicker2 & " " & DTPicker4)

mins = DateDiff("n", DateA, DateB)
MsgBox "mins = " & mins

i get a type mismatch
where DTPicker 1 and 2 contain dates
and 3 and 4 contain time. What can i do ?
My dear friend,

Date picker holds both date and time, so you need to extract date as separate and time as separate,

i am giving example try this.....

Private Sub Command3_Click()
Dim DateA As Date, DateB As Date

DateA = CDate(format(DTPicker1,"MMM/dd/yyyy") & " " & format(DTPicker3,"h:m:s"))
DateB = CDate(format(DTPicker2,"MMM/dd/yyyy") & " " & format(DTPicker4,"h:m:s"))

mins = DateDiff("n", DateA, DateB)
MsgBox "mins = " & mins