Time date query (1 Viewer)

[pickslides]

I have a table with a date field as short text datatype.

Data looks like 2015-05-22 04:45:10.977

MY query wont work when criteria is Between "2015-07-01" And "2015-10-01"

Any tips?

 

[CJ_London]

it is difficult to suggest what you are doing wrong when you don't post the actual code you are using. If it is a written:

1. you are trying to compare a datetype field with texttype field
2. dates have to be in the American style of mm/dd/yyyy and surrounded by #

mydatefield Between #07/01/2015# And #10/01/2015#

 

[pickslides]

Here is the code

TRANSFORM Sum([amount_cents]/100) AS RAP
SELECT Fddvs.display_text
FROM ((Cases LEFT JOIN Fddvs AS Fddvs_1 ON Cases.assignee_group_id = Fddvs_1.entity_id) INNER JOIN Payments ON Cases.id = Payments.case_id) LEFT JOIN Fddvs ON Cases.assignee_group_id = Fddvs.entity_id
WHERE (((Fddvs.field_name)="catchment") AND ((Fddvs_1.entity_type)="Payment") AND ((Fddvs_1.field_name)="expenditure_category") AND ((Cases.created_at) Between "#1/07/2015#" And "#1/10/2015#"))
GROUP BY Fddvs.display_text
PIVOT Fddvs_1.display_text;

 

[CJ_London]

per my point 2 this is for Cases.created_at between 7th Jan 2015 and 10th Jan 2015 - how many records do you expect to be returned?

 

[MarlaC]

Also, if you have the time in your date field, as you do, then to include results from the date input as your ending criteria date, you need to use the DateAdd function to add one day to the end date. This is because dates that are just m/d/yyyy are evaluated to midnight, the very beginning of that day, so times after midnight aren't valid results.

So to include cases created on Jan 10, 2015,
((Cases.created_at) Between "#1/07/2015#" And "#1/10/2015#"))
would need to be changed to either (note removal of quotes, which indicates string, not date)
((Cases.created_at) Between #1/07/2015# And #1/11/2015#))
or
((Cases.created_at) Between #1/07/2015# And DateAdd("d",1,#1/10/2015#)))

 

[CJ_London]

@MarlaC - good spot!