Re: Advanced Query formulas on Date field types vs. Datetime types
I would suggest the unix epoch time to perform the date/time manipulation. The date/time is stored in the database as unix epoch time which is the total number of seconds from 1/1/1970 to till the required date. The date like 24/12/2010 will be stored as the total number of seconds from 1/1/1970 to till date. Identify the date field in the database for which u want the manipulation, bring that in the calculation. *Regards,* *Mahendra Mahalkar* On Wed, Aug 25, 2010 at 9:13 AM, Charles Baldi wrote: > ** Since a date field contains a measurement of "days", you should be able > to do an advanced search with > > Mydate >= $DATE + 7 > > Not in front of a system right now but I believe I've done that myself. > The trick is remembering that it is a date field rather than a datetime > field to know how to perform the search. > > Regards, > Chuck Baldi > > > > On Tue, Aug 24, 2010 at 6:34 PM, Anderson Debra (PRN) < > debra_ander...@prn.com> wrote: > >> ** >> >> Hi Everyone, >> >> >> >> We’re on Remedy 7.0. >> >> >> >> A user came to be with a problem that I never really thought about before >> and I can’t seem to find a good solution. >> >> >> >> We have some forms that use Date datatype for some fields instead of >> Datetime date types. >> >> >> >> For Datetime data type we have many users that do advanced search queries >> like this for example searching for anything 7 days or more in the future. >> >> >> >> Mydate >= $TIMESTAMP$ + (24*7*60*60) >> >> >> >> For Date datatypes this type of syntax does not work no records are >> returned. You can use the constant value $TIMESTAMP$ or $DATE$ but not do >> any math with it. >> >> Does anyone have any ideas how this might be accomplished with an Advanced >> query? The other area where this might be needed/used is for a Table field >> qualification but I think if the advanced query can work so can the table >> qualification. >> >> >> >> Thanks for any tips you might have! >> >> >> Debra >> >> -- -- -- -- -- -- -- -- -- -- >> >> Debra Anderson >> 415 808 9118 direct | >> 600 Harrison St., 4th Floor, San Francisco CA 94107 >> >> Tomorrow's Network for Today's Shopper >> >> *http://www.prn.com >> * >> P *Think Green-* please do not print this email unless necessary >> >> This e-mail (including any attachments) is meant for only the intended >> recipient of the transmission, and may include confidential information. If >> you are not the intended recipient or you received this e-mail in error, any >> review, use, dissemination, distribution, or copying of this e-mail is >> strictly prohibited. If you have received this message in error, please >> notify the sender immediately by telephone at (415) 808-3500 or by return >> e-mail and delete this e-mail, along with any attachments and copies, from >> your system. Thank you. >> >> >> _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ > > > _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ > ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: Advanced Query formulas on Date field types vs. Datetime types
Since a date field contains a measurement of "days", you should be able to do an advanced search with Mydate >= $DATE + 7 Not in front of a system right now but I believe I've done that myself. The trick is remembering that it is a date field rather than a datetime field to know how to perform the search. Regards, Chuck Baldi On Tue, Aug 24, 2010 at 6:34 PM, Anderson Debra (PRN) < debra_ander...@prn.com> wrote: > ** > > Hi Everyone, > > > > We’re on Remedy 7.0. > > > > A user came to be with a problem that I never really thought about before > and I can’t seem to find a good solution. > > > > We have some forms that use Date datatype for some fields instead of > Datetime date types. > > > > For Datetime data type we have many users that do advanced search queries > like this for example searching for anything 7 days or more in the future. > > > > Mydate >= $TIMESTAMP$ + (24*7*60*60) > > > > For Date datatypes this type of syntax does not work no records are > returned. You can use the constant value $TIMESTAMP$ or $DATE$ but not do > any math with it. > > Does anyone have any ideas how this might be accomplished with an Advanced > query? The other area where this might be needed/used is for a Table field > qualification but I think if the advanced query can work so can the table > qualification. > > > > Thanks for any tips you might have! > > > Debra > > -- -- -- -- -- -- -- -- -- -- > > Debra Anderson > 415 808 9118 direct | > 600 Harrison St., 4th Floor, San Francisco CA 94107 > > Tomorrow's Network for Today's Shopper > > *http://www.prn.com > * > P *Think Green-* please do not print this email unless necessary > > This e-mail (including any attachments) is meant for only the intended > recipient of the transmission, and may include confidential information. If > you are not the intended recipient or you received this e-mail in error, any > review, use, dissemination, distribution, or copying of this e-mail is > strictly prohibited. If you have received this message in error, please > notify the sender immediately by telephone at (415) 808-3500 or by return > e-mail and delete this e-mail, along with any attachments and copies, from > your system. Thank you. > > > _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Advanced Query formulas on Date field types vs. Datetime types
Hi Everyone, We're on Remedy 7.0. A user came to be with a problem that I never really thought about before and I can't seem to find a good solution. We have some forms that use Date datatype for some fields instead of Datetime date types. For Datetime data type we have many users that do advanced search queries like this for example searching for anything 7 days or more in the future. Mydate >= $TIMESTAMP$ + (24*7*60*60) For Date datatypes this type of syntax does not work no records are returned. You can use the constant value $TIMESTAMP$ or $DATE$ but not do any math with it. Does anyone have any ideas how this might be accomplished with an Advanced query? The other area where this might be needed/used is for a Table field qualification but I think if the advanced query can work so can the table qualification. Thanks for any tips you might have! Debra -- -- -- -- -- -- -- -- -- -- Debra Anderson 415 808 9118 direct | 600 Harrison St., 4th Floor, San Francisco CA 94107 Tomorrow's Network for Today's Shopper http://www.prn.com P Think Green- please do not print this email unless necessary This e-mail (including any attachments) is meant for only the intended recipient of the transmission, and may include confidential information. If you are not the intended recipient or you received this e-mail in error, any review, use, dissemination, distribution, or copying of this e-mail is strictly prohibited. If you have received this message in error, please notify the sender immediately by telephone at (415) 808-3500 or by return e-mail and delete this e-mail, along with any attachments and copies, from your system. Thank you. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" <>
