Results 1 to 7 of 7

Thread: a difference of 1 hour between 2 dates

  1. #1
    Join Date
    Oct 2004
    Posts
    27

    a difference of 1 hour between 2 dates

    how can I get the datas from a table where dateOut - dateIn > 1 hour

    Select id from myTable WHERE dateOut - dateIn > 1

    return > 1 day

    I have tried :

    Select id from myTable WHERE hour(dateOut - dateIn) > 1

    the difference is ok but between dates not in the same day !!!


    how can I do that ?

    thank you

  2. #2
    Join Date
    Sep 2003
    Location
    in my cube
    Posts
    95
    Use the DateDiff function:

    Private Sub x()
    Dim diff As Date
    Dim DateBegin As Date
    Dim DateEnd As Date
    DateBegin = Now() 'substitute your begin date here
    DateEnd = Now() 'substitute your end date here

    diff = DateDiff("h", DateBegin, DateEnd)
    Debug.Print Hour(diff)
    End Sub

  3. #3
    Join Date
    Oct 2004
    Posts
    27

    ???

    I am looking for SQL not for server side language

    of course it is very easy to do with vb net

    thank you

  4. #4
    Join Date
    Sep 2003
    Location
    in my cube
    Posts
    95
    in your sql query, set up an alias for a new column, and name it Diff:

    Diff : DateDiff("h", DateBegin, DateEnd)

    then set the criteria for Diff on the criteria row as >1

  5. #5
    Join Date
    Oct 2004
    Posts
    27
    in access I get an error

    operator or coma or quote missing

    I have tried exactly your exemple
    Diff : DateDiff("h",DateStart,DateEnd)

    does it work for you in access ?

  6. #6
    Join Date
    Sep 2003
    Location
    in my cube
    Posts
    95
    I left off the brackets. Access added the brackets immediately and it worked.

    Diff: DateDiff("h",[DateStart],[DateEnd])


    I've attached a table with two records, one whose timeframe is 1 minute apart, another greater than an hour, and the criteria is > 1.

    Only the record greater than an hour is returned in the resultset.
    Attached Files Attached Files

  7. #7
    Join Date
    Oct 2004
    Posts
    27
    great !!

    I keep it warm somewhere (as french says)

    thanks a ton for your help !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •