Re: Calculating a Date only Field
A Date field (not a Date/Time that displays just the date) should be : 'Expiration Date' = ($DATE$ + 30) Fred From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Kemes, Lisa Sent: Wednesday, January 30, 2008 10:26 AM To: arslist@ARSLIST.ORG Subject: Calculating a Date only Field I know how to calculate when something is going to expire in 30 days if the field is a Date/Time field. But what about just a Date field? Here's how I would figure it out it if it was a Date/Time field: ( 'Expiration Date' >= ($DATE$ + (((30 * 24) * 60) * 60))) AND ( 'Expiration Date' < ($DATE$ + (((31 * 24) * 60) * 60))) Lisa Kemes AR System Developer Tyco Electronics 717-810-2408 tel 717-810-2124 fax [EMAIL PROTECTED] ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
Re: Calculating a Date only Field
Calculating a Date only FieldFred,
This won't work in a search qualification, because the $DATE$ keyword will
resolve to epoch time (# of seconds since January 1, 1970 12:00:00 AM GMT).
Theoretically, the following *should* work (but it returns an error "ARERR
[312] Data types are not appropriate for arithmetic operation"):
(Search qual): 'Expiration Date' = 2440588 + $DATE$/86400 + 30
The integer '2440588' represents the date 01-JAN-1970, and the expression
$DATE$/86400 should return an integer representing the number of days offset
for the current date from 01-JAN-1970 (after dropping the remainder).
Unfortunately, this calculation causes an error when used with a Date-only
field.
So... if you are simply running a search in the Advanced Search bar, you would
have to calculate this value in advance. For example:
$DATE$ = 01/30/2008 12:00:00 AM CST = 01/30/2008 06:00:00 AM GMT = 1201672800
seconds
$DATE$/86400 = 1201672800/86400 = 13908.25 days
Once you have the whole number of days offset from 1/1/1970, substituting this
value in the search qualification should work in the Advanced Search bar:
(Search qual): 'Expiration Date' = 2440588 + 13908 + 30
If you need to perform this search in a workflow/run-if qualification, simply
use a temp integer field to store the value of $DATE$/86400, then use this
value in the qualification. For example:
(Set field): 'zTmpInt' = $DATE$/86400
(Search qual): 'Expiration Date' = 2440588 + $zTmpInt$ + 30
Alternately, you could also set a temp date-only field (e.g., 'zTmpDate'),
using the DATEADD function:
(Set field): 'zTmpDate' = DATEADD("dd", 30, $Expiration Date$)
Then use the zTmpDate field as the search qualifier:
(Search qual): 'Expiration Date' = 'zTmpDate'
Hope this helps!
--Thomas
- Original Message -
From: Grooms, Frederick W
Newsgroups: gmane.comp.crm.arsystem.general
To: arslist@ARSLIST.ORG
Sent: Wednesday, January 30, 2008 10:52 AM
Subject: Re: Calculating a Date only Field
**
A Date field (not a Date/Time that displays just the date) should be :
'Expiration Date' = ($DATE$ + 30)
Fred
--
From: Action Request System discussion list(ARSList) [mailto:[EMAIL
PROTECTED] On Behalf Of Kemes, Lisa
Sent: Wednesday, January 30, 2008 10:26 AM
To: arslist@ARSLIST.ORG
Subject: Calculating a Date only Field
I know how to calculate when something is going to expire in 30 days if the
field is a Date/Time field. But what about just a Date field?
Here's how I would figure it out it if it was a Date/Time field:
( 'Expiration Date' >= ($DATE$ + (((30 * 24) * 60) * 60))) AND ( 'Expiration
Date' < ($DATE$ + (((31 * 24) * 60) * 60)))
Lisa Kemes
AR System Developer
Tyco Electronics
717-810-2408 tel
717-810-2124 fax
[EMAIL PROTECTED]
__Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
Re: Calculating a Date only Field (correction)
Calculating a Date only FieldDoh!
That last part should read:
Alternately, you could also set a temp date-only field (e.g., 'zTmpDate'),
using the DATEADD function:
(Set field): 'zTmpDate' = DATEADD("dd", 30, $DATE$)
Then use the zTmpDate field as the search qualifier:
(Search qual): 'Expiration Date' = 'zTmpDate'
--Thomas
- Original Message -
From: Thomas Bean
To: arslist@ARSLIST.ORG
Sent: Wednesday, January 30, 2008 11:35 AM
Subject: Re: Calculating a Date only Field
Fred,
This won't work in a search qualification, because the $DATE$ keyword will
resolve to epoch time (# of seconds since January 1, 1970 12:00:00 AM GMT).
Theoretically, the following *should* work (but it returns an error "ARERR
[312] Data types are not appropriate for arithmetic operation"):
(Search qual): 'Expiration Date' = 2440588 + $DATE$/86400 + 30
The integer '2440588' represents the date 01-JAN-1970, and the expression
$DATE$/86400 should return an integer representing the number of days offset
for the current date from 01-JAN-1970 (after dropping the remainder).
Unfortunately, this calculation causes an error when used with a Date-only
field.
So... if you are simply running a search in the Advanced Search bar, you
would have to calculate this value in advance. For example:
$DATE$ = 01/30/2008 12:00:00 AM CST = 01/30/2008 06:00:00 AM GMT =
1201672800 seconds
$DATE$/86400 = 1201672800/86400 = 13908.25 days
Once you have the whole number of days offset from 1/1/1970, substituting
this value in the search qualification should work in the Advanced Search bar:
(Search qual): 'Expiration Date' = 2440588 + 13908 + 30
If you need to perform this search in a workflow/run-if qualification, simply
use a temp integer field to store the value of $DATE$/86400, then use this
value in the qualification. For example:
(Set field): 'zTmpInt' = $DATE$/86400
(Search qual): 'Expiration Date' = 2440588 + $zTmpInt$ + 30
Alternately, you could also set a temp date-only field (e.g., 'zTmpDate'),
using the DATEADD function:
(Set field): 'zTmpDate' = DATEADD("dd", 30, $Expiration Date$)
Then use the zTmpDate field as the search qualifier:
(Search qual): 'Expiration Date' = 'zTmpDate'
Hope this helps!
--Thomas
- Original Message -
From: Grooms, Frederick W
Newsgroups: gmane.comp.crm.arsystem.general
To: arslist@ARSLIST.ORG
Sent: Wednesday, January 30, 2008 10:52 AM
Subject: Re: Calculating a Date only Field
**
A Date field (not a Date/Time that displays just the date) should be :
'Expiration Date' = ($DATE$ + 30)
Fred
From: Action Request System discussion list(ARSList) [mailto:[EMAIL
PROTECTED] On Behalf Of Kemes, Lisa
Sent: Wednesday, January 30, 2008 10:26 AM
To: arslist@ARSLIST.ORG
Subject: Calculating a Date only Field
I know how to calculate when something is going to expire in 30 days if the
field is a Date/Time field. But what about just a Date field?
Here's how I would figure it out it if it was a Date/Time field:
( 'Expiration Date' >= ($DATE$ + (((30 * 24) * 60) * 60))) AND (
'Expiration Date' < ($DATE$ + (((31 * 24) * 60) * 60)))
Lisa Kemes
AR System Developer
Tyco Electronics
717-810-2408 tel
717-810-2124 fax
[EMAIL PROTECTED]
__Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
html___
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
