473,390 Members | 1,300 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

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

anoble1
245 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 4764
isladogs
455 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,556 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

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

Similar topics

6
by: Piotr Pietrowski | last post by:
Hello everybody, I have a *big* problem. I thought its not that big problem for you professionals... Anyway, I have a begin date which has 3 dropdown boxes (day/Month/Year). The same for the...
4
by: William Bradley | last post by:
I have two cells on a form. One of them is the "Production Date" and the other is the "Expiry Date". The "Expiry Date" is 183 days after the "Production Date." On an Excel spreadsheet, the...
2
by: William Bradley | last post by:
"Marshall Barton" <marshbarton@wowway.com> wrote in message news:9as9lvgpnp783kogctb88c8giaepb5uf6g@4ax.com... > William Bradley wrote: > >I have two cells on a form. One of them is the...
4
by: S. van Beek | last post by:
Dear reader, By a Date field with Now() as default value the content of the field is date plus time. As I need a filter in a query on date only (excluding time) I invented the following...
4
by: Daniel Kaseman | last post by:
How do I convert a date into a serial number? (I'm trying to enter a FROM date and a TO date, then make my PROGRESS BAR show how close I am to the TO date.) get it? I know that MS Excel...
6
by: vijayk | last post by:
Hi all, I have a field which has data as YYYYMMDD, and I have to find the age of the person by substracting it from current date. can you please please advice... thanks
4
by: jamesyreid | last post by:
Hi, I'm really sorry to post this as I know it must have been asked countless times before, but I can't find an answer anywhere. Does anyone have a snippet of JavaScript code I could borrow...
6
by: krishnakant Mane | last post by:
hello, I am strangely confused with a date calculation problem. the point is that I want to calculate difference in two dates in days. there are two aspects to this problem. firstly, I can't get...
2
tuxalot
by: tuxalot | last post by:
I have a textbox, on with a record source . FrmMain is a tabbed form showing injury data for a given EmployeesID. I have a textbox, on a subform . with as the record source. is entered by a...
2
by: lohima | last post by:
hi i want to get date difference between two columns. month and DOJ in days. month is integer datatype and doj is in time stamp. the colums data was like this. i mentioned '|' in btw of 2 columns...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

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.