469,326 Members | 1,320 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

Is there a way to do a Date Difference with a Date Serial?

anoble1
235 100+
I have a table that I am using the run a query. The date format is yyyymmdd. I can't get the DateAdd function to work. I am doing a
Expand|Select|Wrap|Line Numbers
  1. =DateAdd("d",-100,Now())
and comes back blank with records from 3 years ago till today.
My question - Is there a better way to do this? If you have a date that is in yyyymmdd how do you tell the query you want to only see records that are older than 100 days from today?
4 Weeks Ago #1

✓ answered by isladogs

One method is to use a filter in your query:

Expand|Select|Wrap|Line Numbers
  1. WHERE [YourDateField]<Date()-100

2 3819
isladogs
291 Expert 256MB
One method is to use a filter in your query:

Expand|Select|Wrap|Line Numbers
  1. WHERE [YourDateField]<Date()-100
4 Weeks Ago #2
NeoPa
32,181 Expert Mod 16PB
If you want to do the same thing, but without relying on how dates are held and manipulated in memory, then the following also works :
Expand|Select|Wrap|Line Numbers
  1. WHERE ([YourDateField]<DateAdd('d',-100,Date()))
Remember, calculating the value that the dates need to be compared with is always better than calculating the difference based on the field value as the former calculation need only be done once whereas the latter need be done for each record. This can have further reach than simply slowing down each record.

It also helps to understand the difference between the Date() & Now() functions and which values they return. Once understood they can be used appropriately - as illustrated by IslaDogs' example.

PS. Don't get me wrong. It is 100% reliable to work with days as integer values when using dates. This isn't going to change. However, for purists - and the potential for porting the code to other less defined systems - using DateAdd() is advisable.
4 Weeks Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Piotr Pietrowski | last post: by
4 posts views Thread by William Bradley | last post: by
2 posts views Thread by William Bradley | last post: by
4 posts views Thread by Daniel Kaseman | last post: by
6 posts views Thread by vijayk | last post: by
6 posts views Thread by krishnakant Mane | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.