Yes, I am referring to Date Only fields. You have provided exactly the information I've been searching for. And your formula worked perfectly, Fred! Thank you so much!!
Jenni Wacholz Remedy Administration Coventry Health Care Inc 480-445-2517 ________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Friday, August 03, 2007 2:14 PM To: [email protected] Subject: Re: DATE fields conversion formula for use in Crystal ** Are you talking a Date Only field or a Date/Time set to only show dates? From the help text in the Admin tool ARS stores date and time values as follows: * Date/Time values-As integers relative to 00:00:00 GMT, January 1, 1970. The display format and time zone offset are based on the environment of the user, even when multiple clients view the same form from different time zones. * Date values-As integers relative to 1/1/4713 B.C. Date values are displayed in Gregorian format and are not based on time zone. So a date only field of Jan 1, 1970 = 2440588 I think the conversion for a Date Only value would be datetimevar convdate; convdate :=(DateAdd ("d",({BC_TEST.DATE_TIME_FIELD}-2440587),datetime(1970,01,01))); convdate; Fred ________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Wacholz, Jeanette (Jenni) Sent: Friday, August 03, 2007 3:43 PM To: [email protected] Subject: Re: DATE fields conversion formula for use in Crystal ** This is really weird then. Because the integer value I see in Crystal for the date 7/24/2007 is 2,454,306...much smaller than the integer value for the same date in a date/time field. I've opened a ticket to BMC on this. I'll let you know what I get back from them. Thanks again for your time and effort, Ben!!! Jenni Wacholz Remedy Administration Coventry Health Care Inc 480-445-2517 ________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ben Cantatore Sent: Friday, August 03, 2007 1:32 PM To: [email protected] Subject: Re: DATE fields conversion formula for use in Crystal ** I'm not sure what's wrong. I created a dummy form and made one entry with a date only field. When I ran the report using datetimevar convdate; convdate :=(DateAdd ("s",({BC_TEST.DATE_TIME_FIELD}),datetime(1970,01,01))); convdate; as the formula, it gave me 8/22/07 (the date I inputed into the form) and 4:00 am. A date only field is really stored as a date time field. The time portion is set to 00:00:00. So converting it like a date time field should apply. Below is a sample report using the create date (datetime) and the Date field (Date only). I made two entries into this form. REQUEST_ID CREATE_DATE Create Da DATE DateConvers 00000000000001 1,186,171,068 8/3/07 1,187,755,200.00 8/22/07 00000000000002 1,186,172,457 8/3/07 1,186,113,600.00 8/3/07 It should work. Ben Cantatore Remedy Administrator Avon (914) 935-2946 "Wacholz, Jeanette (Jenni)" <[EMAIL PROTECTED]> Sent by: "Action Request System discussion list(ARSList)" <[email protected]> 08/03/2007 02:48 PM Please respond to [email protected] To [email protected] cc Subject Re: DATE fields conversion formula for use in Crystal ** Thanks, Ben, but unless I'm misreading it, that seems to be a formula for converting date/time fields, not date fields. I tried it and the Date field value of 2454306, which in Remedy is converted to 7/24/2007, is converted in Crystal to 1/29/1970. I then thought maybe the date field integer value referred to the number of days (instead of seconds) since 1/1/1970, but that converts the integer value to 8/28/2020 so that can't be right either. Somewhere in the dim recesses of my memory, back when date fields first became available, I seem to recall hearing that the date fields used a different "start" date from the date/time fields. Does that sound familiar to anyone? Sure would be nice if they provided conversion formulas in the BMC/Remedy manuals. Jenni Wacholz Remedy Administration Coventry Health Care Inc 480-445-2517 ________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ben Cantatore Sent: Friday, August 03, 2007 11:22 AM To: [email protected] Subject: Re: DATE fields conversion formula for use in Crystal ** I use something like this: datetimevar convdate; convdate :=(DateAdd ("s",({CHG_INFRASTRUCTURE_CHANGE.SCHEDULED_START_DATE}),datetime(1970,01 ,01))); convdate := dateadd ("h", [EMAIL PROTECTED], convdate); convdate; I'd have a formula for timezone depending on what timezone the reported needed to show. If you only have to worry about one timezone, then something like: DateAdd ("s",{PBM_Investigation_WorkLog.WORK_LOG_SUBMIT_DATE},#12/31/1969 7:00 pm#) would work. Just adjust 7:00 pm for whatever works for you. I'm in NY so I'm minus -5 GMT. Ben Cantatore Remedy Administrator Avon (914) 935-2946 "Wacholz, Jeanette (Jenni)" <[EMAIL PROTECTED]> Sent by: "Action Request System discussion list(ARSList)" <[email protected]> 08/03/2007 02:09 PM Please respond to [email protected] To [email protected] cc Subject DATE fields conversion formula for use in Crystal ** Does anyone have a Crystal formula for converting DATE fields from integer to actual date? We are not able to use the Remedy ODBC driver for several reasons. I have the set of ESP formulas for converting date/time fields (and they are terrific!) but they aren't designed to work with date only fields. Jenni Wacholz Remedy Administration Coventry Health Care Inc 480-445-2517 __20060125_______________________This posting was submitted with HTML in it___ Email Confidentiality Notice: The information contained in this transmission is confidential, proprietary or privileged and may be subject to protection under the law, including the Health Insurance Portability and Accountability Act (HIPAA). The message is intended for the sole use of the individual or entity to whom it is addressed. If you are not the intended recipient, you are notified that any use, distribution or copying of the message is strictly prohibited and may subject you to criminal or civil penalties. If you received this transmission in error, please contact the sender immediately by replying to this email and delete the material from any computer. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
