Re: Convert Date in to timestamp in remedy
You need to take care of Time Zone, by default it is GMT, you need to add / subtract time in sql query according to your local time zone. Warm Regards, Jignesh Shah -- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Benjamin Trimmer Sent: Tuesday, October 02, 2007 6:29 PM To: arslist@ARSLIST.ORG Subject: Re: Convert Date in to timestamp in remedy ** UPDATE [table2] SET [field_to_modify] = DATEADD(s, [Remedy_DT_Field] + [additional time in seconds if necessary], '01-01-1970 00:00:00') WHERE [criteria] Regards, Ben Trimmer Atul Vohra [EMAIL PROTECTED] Sent by: Action Request System discussion list(ARSList) arslist@ARSLIST.ORG 10/02/2007 07:49 AM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Re: Convert Date in to timestamp in remedy ** Hi Benjamin, Appreciate your response but what I am looking for is an update statement that sets the date field using a Remedy date field. Atul - Original Message - From: Benjamin Trimmer To: arslist@ARSLIST.ORG Subject: Re: Convert Date in to timestamp in remedy Date: Tue, 2 Oct 2007 07:12:14 -0500 ** Atul. This query will return you a Remedy date/time field as a SQL date: SELECT DATEADD(s, [Remedy_DT_Field], '01-01-1970 00:00:00' FROM [tablename] Regards, Ben Trimmer Atul Vohra [EMAIL PROTECTED] Sent by: Action Request System discussion list(ARSList) arslist@ARSLIST.ORG 10/01/2007 10:27 PM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Convert Date in to timestamp in remedy Hi, We are using ARS 6.3, MS SQL. I want to update a date field using direct sql - can some one help me with this. Is there a function to convert the date field in to timestamp or can I use the sql to update the date field directly. Thanks Atul ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are * * * * * * * * * * * * * * * * * * * * * * * * * IMPORTANT NOTICE* * * * * * * * * * * * * * * * * * * * * * * * * Unless otherwise indicated or obvious from the nature of the transmittal, the information contained in this email message is CONFIDENTIAL information intended for the use of the individual or entity named herein. If the reader of this message is not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender using the above contact information or by return email and delete this message and any copies from your computer system. Thank you. __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Convert Date in to timestamp in remedy
Atul. This query will return you a Remedy date/time field as a SQL date: SELECT DATEADD(s, [Remedy_DT_Field], '01-01-1970 00:00:00' FROM [tablename] Regards, Ben Trimmer Atul Vohra [EMAIL PROTECTED] Sent by: Action Request System discussion list(ARSList) arslist@ARSLIST.ORG 10/01/2007 10:27 PM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Convert Date in to timestamp in remedy Hi, We are using ARS 6.3, MS SQL. I want to update a date field using direct sql - can some one help me with this. Is there a function to convert the date field in to timestamp or can I use the sql to update the date field directly. Thanks Atul ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are * * * * * * * * * * * * * * * * * * * * * * * * * IMPORTANT NOTICE* * * * * * * * * * * * * * * * * * * * * * * * * Unless otherwise indicated or obvious from the nature of the transmittal, the information contained in this email message is CONFIDENTIAL information intended for the use of the individual or entity named herein. If the reader of this message is not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender using the above contact information or by return email and delete this message and any copies from your computer system. Thank you. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Convert Date in to timestamp in remedy
Hi Benjamin, Appreciate your response but what I am looking for is an update statement that sets the date field using a Remedy date field. Atul - Original Message - From: Benjamin Trimmer To: arslist@ARSLIST.ORG Subject: Re: Convert Date in to timestamp in remedy Date: Tue, 2 Oct 2007 07:12:14 -0500 ** Atul. This query will return you a Remedy date/time field as a SQL date: SELECT DATEADD(s, [Remedy_DT_Field], '01-01-1970 00:00:00' FROM [tablename] Regards, Ben Trimmer Atul Vohra [EMAIL PROTECTED] Sent by: Action Request System discussion list(ARSList) arslist@ARSLIST.ORG 10/01/2007 10:27 PM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Convert Date in to timestamp in remedy Hi, We are using ARS 6.3, MS SQL. I want to update a date field using direct sql - can some one help me with this. Is there a function to convert the date field in to timestamp or can I use the sql to update the date field directly. Thanks Atul ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are * * * * * * * * * * * * * * * * * * * * * * * * * IMPORTANT NOTICE* * * * * * * * * * * * * * * * * * * * * * * * * Unless otherwise indicated or obvious from the nature of the transmittal, the information contained in this email message is CONFIDENTIAL information intended for the use of the individual or entity named herein. If the reader of this message is not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender using the above contact information or by return email and delete this message and any copies from your computer system. Thank you. __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Convert Date in to timestamp in remedy
UPDATE [table2] SET [field_to_modify] = DATEADD(s, [Remedy_DT_Field] + [additional time in seconds if necessary], '01-01-1970 00:00:00') WHERE [criteria] Regards, Ben Trimmer Atul Vohra [EMAIL PROTECTED] Sent by: Action Request System discussion list(ARSList) arslist@ARSLIST.ORG 10/02/2007 07:49 AM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Re: Convert Date in to timestamp in remedy ** Hi Benjamin, Appreciate your response but what I am looking for is an update statement that sets the date field using a Remedy date field. Atul - Original Message - From: Benjamin Trimmer To: arslist@ARSLIST.ORG Subject: Re: Convert Date in to timestamp in remedy Date: Tue, 2 Oct 2007 07:12:14 -0500 ** Atul. This query will return you a Remedy date/time field as a SQL date: SELECT DATEADD(s, [Remedy_DT_Field], '01-01-1970 00:00:00' FROM [tablename] Regards, Ben Trimmer Atul Vohra [EMAIL PROTECTED] Sent by: Action Request System discussion list(ARSList) arslist@ARSLIST.ORG 10/01/2007 10:27 PM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Convert Date in to timestamp in remedy Hi, We are using ARS 6.3, MS SQL. I want to update a date field using direct sql - can some one help me with this. Is there a function to convert the date field in to timestamp or can I use the sql to update the date field directly. Thanks Atul ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are * * * * * * * * * * * * * * * * * * * * * * * * * IMPORTANT NOTICE* * * * * * * * * * * * * * * * * * * * * * * * * Unless otherwise indicated or obvious from the nature of the transmittal, the information contained in this email message is CONFIDENTIAL information intended for the use of the individual or entity named herein. If the reader of this message is not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender using the above contact information or by return email and delete this message and any copies from your computer system. Thank you. __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Convert Date in to timestamp in remedy
Atul, I have attached copies of the MS-SQL functions that I use to convert Remedy date/time and date values. Hopefully you will find one of these helpful for what you are attempting to accomplish. convert_date_to_epoch.sql: converts MS-SQL datetime values (or any string that represents a valid datetime value) to the epoch date format used to store Remedy date/time fields in the database (# of seconds since January 1, 1970 12:00:00 AM GMT). The last section of the function must be adjusted to represent the correct offsets (in seconds) from GMT in your time zone for standard time and daylight saving (summer) time. convert_epoch_to_date.sql: converts the epoch date format used to store Remedy date/time values in the database (# of seconds since January 1, 1970 12:00:00 AM GMT) to MS-SQL datetime format. The last section of the function must be adjusted to represent the correct offsets (in seconds) from GMT in your time zone for standard time and daylight saving (summer) time. datetime_to_DateOnlyInt.sql: converts MS-SQL datetime values (or any string that represents a valid datetime value) to the integer value used to store Remedy date fields in the database (# of days since January 1, 4713, B.C.) DateOnlyInt_to_datetime.sql: converts the integer value used to store Remedy date fields in the database (# of days since January 1, 4713, B.C.) to MS-SQL datetime format. MS-SQL datetime values do not go back this far, so the function will always return the minimum MS-SQL datetime value (January 1, 1753) for any Remedy date values prior to this range. Regards, Thomas Bean - Original Message - From: Atul Vohra [EMAIL PROTECTED] Newsgroups: gmane.comp.crm.arsystem.general To: arslist@ARSLIST.ORG Sent: Monday, October 01, 2007 10:27 PM Subject: Convert Date in to timestamp in remedy Hi, We are using ARS 6.3, MS SQL. I want to update a date field using direct sql - can some one help me with this. Is there a function to convert the date field in to timestamp or can I use the sql to update the date field directly. Thanks Atul ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are datetime_to_DateOnlyInt.sql Description: Binary data convert_date_to_epoch.sql Description: Binary data convert_epoch_to_date.sql Description: Binary data DateOnlyInt_to_datetime.sql Description: Binary data
Convert Date in to timestamp in remedy
Hi, We are using ARS 6.3, MS SQL. I want to update a date field using direct sql - can some one help me with this. Is there a function to convert the date field in to timestamp or can I use the sql to update the date field directly. Thanks Atul ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Convert Date in to timestamp in remedy
Have you tried doing it at application level? You could have a temporary character field to set the Date from the Date type field and then append the time part as 12:00:00 AM to the value in the character field separated by a space, and set it to the Date/Time field. Joe D'Souza -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Atul Vohra Sent: Monday, October 01, 2007 11:28 PM To: arslist@ARSLIST.ORG Subject: Convert Date in to timestamp in remedy Hi, We are using ARS 6.3, MS SQL. I want to update a date field using direct sql - can some one help me with this. Is there a function to convert the date field in to timestamp or can I use the sql to update the date field directly. Thanks Atul No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.488 / Virus Database: 269.13.37/1042 - Release Date: 10/1/2007 6:59 PM ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
