How to retrieve results by date which has date and time in the field

[rayted]

Hi guys,

I am trying to run a query to return results within the past i.e. date()-1 etc.

However, the source has this format: 28/04/2017 17:00:09. The source is from a linked table (pulling emails from a folder on outlook)

Therefore, the query is returning no results. How do I get around this?



Thank you

[CJ_London]

date formats are irrelevant - in the underlying value, dates are stored as decimal numbers, the bit before the dp represents the date counting up from 31/12/1899 as 0 so today is 43217. The bit after the dp represents the time as the time in seconds divided by 86400 - the number of seconds in the day - so 8:51am=0.3680324074

so if your field contains a time you are trying to find say 43217.3680324074 < 43218-1 - which it isn't so isn't returned.

solution depends on what you are trying to do - either add 1 to your search date or just compare the date part of the search date

fielddate<=date()+1-1

or

datevalue(fielddate)<=date()-1

[rayted]

Hi Ajax,

Your suggestions do not work unfortunately. I am still not getting any results.

Is there a way I can build the left function in to return results? Maybe using expression builder? Then I can just retrieve the first part of the date?

Thanks!

*UPDATE* I have got the date using the left function! should be good for now. Thanks your assistance Ajax!

[CJ_London]

without knowing your code, impossible to say why it doesn't work

as advised, be aware that date is stored as a number, not text so I would check carefully what your left function is actually returning - I suspect when you get to the 1st May, it will return 5th Jan

good luck with your project