By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

subtract now() from date/time field in query

P: 39
I'm trying to use this expression in a Query:


Expand|Select|Wrap|Line Numbers
  1. idleTime = now() - dateRequested 
It just spits out gibberish numbers with 20 decimal places though. I can't find a way to specify a date format in query design. Any ideas?

Here's the SQL view code:

Expand|Select|Wrap|Line Numbers
  1. Now()-[DateRequested] AS IdleTime
Feb 23 '07 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,607
I'm trying to use this expression in a Query:


Expand|Select|Wrap|Line Numbers
  1. idleTime = now() - dateRequested 
It just spits out gibberish numbers with 20 decimal places though. I can't find a way to specify a date format in query design. Any ideas?

Here's the SQL view code:

Expand|Select|Wrap|Line Numbers
  1. Now()-[DateRequested] AS IdleTime
Expand|Select|Wrap|Line Numbers
  1. DateDiff("d",Now(), [DateRequested])    'difference in days
Feb 23 '07 #2

P: 39
Thanks - Still learning :)
Feb 23 '07 #3

NeoPa
Expert Mod 15k+
P: 31,263
I'm trying to use this expression in a Query:


Expand|Select|Wrap|Line Numbers
  1. idleTime = now() - dateRequested 
It just spits out gibberish numbers with 20 decimal places though. I can't find a way to specify a date format in query design. Any ideas?

Here's the SQL view code:

Expand|Select|Wrap|Line Numbers
  1. Now()-[DateRequested] AS IdleTime
Dates and Times are stored as numeric values.
To convert to a more meaningful string value use Format().
Expand|Select|Wrap|Line Numbers
  1. idleTime = Format(Now() - dateRequested,"0")
  2. or
  3. idleTime = Format(Now() - dateRequested,"HH:nn:ss")
The former displays the days and the latter the time. A single format string doesn't seem to work but they can be joined together with & if required.
Feb 26 '07 #4

NeoPa
Expert Mod 15k+
P: 31,263
As a continuation, Your IdleTime field, as it stands, is the correct result anyway. It is simply formatted as a decimal number when displayed. It is fine as a store of the correct value. As long as you remember to display it correctly it is not wrong.
Feb 26 '07 #5

Post your reply

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