Hello Satya, Remedy DATE fields are stored as an integer representing the number of days elapsed since January 1, 4713 B.C., in the proleptic Gregorian calendar.
I have posted MS-SQL date conversion functions to the list several times in the past which can convert these integers to a readable date format (as well as convert a SQL date to the integer value). I last posted these on November 12, 2008 (Re: Date Time Question). For your convenience, I have attached one of the functions from my earlier posts, which converts the DATE integer to a SQL datetime value. Keep in mind, MS-SQL datetime values only go back as far as January 1, 1753. So, I wrote the function to simply return the MS-SQL minimum date for any integer values representing earlier dates. Hope this helps, Thomas ----- Original Message ----- From: Satya Gandhi Newsgroups: gmane.comp.crm.arsystem.general To: [email protected] Sent: Tuesday, December 02, 2008 05:31 Subject: DATE field in AR System Hi Listers, All of us know that DATETIME fields in Remedy are stored in the backend as integers; the value being the number of seconds from 1 Jan 1970. How is the DATE field stored in the database? It is stored as integer; but I am not able to decrypt the value. For example - a DATE value of 26 Nov 2008 when set to a integer field produces a value of '2454797'. When i set the same integer value to a DATETIME field i get the value as 29.01.1970 09:53:17 wherein assume that the value that should be 26-Nov-2008 00:00:00 Would someone know how do i convert the integer value for DATE field to a normal date display? We are running a ARS 7.1 p003 on MS SQL 2005. -- Thanks & Regards Satya Gandhi Consultant - Remedy _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
DateOnlyInt_to_datetime.sql
Description: Binary data
