Results 1 to 8 of 8

Thread: Query with time

  1. #1
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts

    Query with time

    Hi

    I have a report that I want only transactions that were entered from 11:01 am to 10:29 am. The results of this time span could result in two days. The table has a field named "Date Entered" where the date is formatted as mm/dd/yyyy and the time is hh:mm:ss pm or am. I am looking for a criteria string that would give the results based on the time span above. I found the following criteria string:
    >=Date() AND <Date()+1 that gives me one day of transactions but not the next day before 11:01 am.

    Hope this is clear.

    Jean

  2. #2
    jwoods
    Guest
    WHERE DateEntered >= '1/1/2015 11:01:00' AND DateEntered <= '1/2/2015 22:29:00'

    See the DATEPART function on how to extract the specific values.
    Last edited by jwoods; 2015-12-15 at 20:14.

  3. #3
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,725
    Thanks
    147
    Thanked 156 Times in 149 Posts
    Just in case of confusion, I note that Jean specified 10:29 am, but jwoods coded it as 10:29 pm. Not sure who has the mistype

  4. #4
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Hi

    My mistake it should be 10:59 am--how do I fix that? Is there a way to make the date option entered in by the user?

    Can I type this on the criteria line in a query?

    Jean
    Last edited by JeanM; 2015-12-16 at 09:11.

  5. #5
    jwoods
    Guest
    Quote Originally Posted by access-mdb View Post
    Just in case of confusion, I note that Jean specified 10:29 am, but jwoods coded it as 10:29 pm. Not sure who has the mistype
    I misread it...

    Change 22:29:00 to 10:29:00

  6. #6
    4 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    561
    Thanks
    51
    Thanked 68 Times in 66 Posts
    In order to fully control the query and the datatypes involved, I'd recommend changing the suggested query slightly:

    WHERE DateEntered >= Cast('2015-01-01 11:01:00' As SmallDateTime) AND DateEntered <= Cast('2015-01-02 10:29:00' As SmallDateTime)
    I've stuck to strict Left To Right ordinality in the dates, and by using the Cast() function I don't depend on the query environment to perform an implicit type change.

  7. #7
    jwoods
    Guest
    No need to CAST anything.

    I would probably put the value of Date Entered into a variable declared as smalldatetime for the left-side date, and put the result of DATEADD to that Date Entered variable with "day" and "1" as the increment, into another variable declared as smalldatetime for the right-side date.

    HH and MM can be checked using DATEPART on both variables.

  8. #8
    New Lounger
    Join Date
    Nov 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm assuming you want all three of those as part of the selection criteria. You'll need a few statements in your where but they will be similar to the link your question contained.

    SELECT *
    FROM MyTable
    WHERE [dateColumn] > '3/1/2009' AND [dateColumn] <= DATEADD(day,'3/31/2009',1)
    --make it inclusive for a datetime type
    AND DATEPART(hh,[dateColumn]) >= 6 AND DATEPART(hh,[dateColumn]) <= 22
    -- gets the hour of the day from the datetime
    AND DATEPART(dw,[dateColumn]) >= 3 AND DATEPART(dw,[dateColumn]) <= 5
    -- gets the day of the week from the datetime

    Hope this helps.
    Last edited by comprar; 2016-05-27 at 05:37.

Posting Permissions

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