471,854 Members | 1,687 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,854 software developers and data experts.

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

anoble1
243 128KB
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?
Sep 22 '21 #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 4667
isladogs
404 Expert Mod 256MB
One method is to use a filter in your query:

Expand|Select|Wrap|Line Numbers
  1. WHERE [YourDateField]<Date()-100
Sep 22 '21 #2
NeoPa
32,462 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.
Sep 24 '21 #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
NeoPa
reply views Thread by NeoPa | last post: by
reply views Thread by YellowAndGreen | last post: by
aboka
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.