I see why casting to an integer would lose any leading zeros but I am having a hard time understanding why epoch time would need a leading zero.
[Moments pass, haven't had first cup of coffee yet] Ohhhhhh! Those example are not doing the math needed to convert to epoch. It is keeping in a date/time looking format but just casting to int. Notice the difference in my example where midnight 1/1/1970 is referenced. That is the epoch "magic." Here is an example of an epoch integer stored in the db: 1336486481. The Remedy clients at runtime convert that to display what we humans like as date/time. Jason On May 8, 2012 6:01 AM, "Nancy Tietz" <[email protected]> wrote: > ** > > Thanks Jason!!! > > > > I got some other examples from another programmer who has a lot of > experience in SQL Server: > > > > I’ll have to try some of these out. I’m not sure what he means by ‘losing > the leading zero’… > > > > I used getdate() as my date in the examples. > > GETDATE() IS LIKE ORACLES SYSDATE > > ----- > > > > --ONE WAY (REMOVE THE COMMENTS FROM THE CAST PART AND IT WILL BE INTEGER > BUT YOU WILL LOSE THE LEADING ZERO > > SELECT > > -- CAST( > > > > RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(HOUR, GETDATE() )), 2) + > > RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MINUTE, GETDATE() )), 2) + > > RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(SECOND, GETDATE() )), 2) + > > RIGHT('0' + CONVERT(VARCHAR(3), DATEPART(MILLISECOND, GETDATE() )), 3) > > > > -- AS INT) > > FROM bbdw.DIM_CURRENCY > > > > > > --ANOTHER WAY BUT STILL NO LEADING ZERO > > SELECT cast( > > replace( > > CONVERT(VARCHAR(20), GETDATE(),114 > > ),':','') > > as int) > > from bbdw.DIM_CURRENCY > > > > --ANOTHER WAY BUT STILL NO LEADING ZERO > > SELECT > > CAST( > > REPLACE ( > > str( > > DATEPART(hour, getdate()) > > ,2) > > + > > str( > > DATEPART(minute, getdate()) > > ,2) > > + > > str( > > DATEPART(second, getdate()) > > ,2) > > + > > str( > > DATEPART(millisecond, getdate()) > > ,3) > > ,' ',0) > > AS INT) > > from bbdw.DIM_CURRENCY > > > > > > > > > > *From:* Action Request System discussion list(ARSList) [mailto: > [email protected]] *On Behalf Of *Jason Miller > *Sent:* Monday, May 07, 2012 6:32 PM > *To:* [email protected] > *Subject:* Re: to commit or not to commit... that is the question > > > > ** Here is an example I used to update record 1 in a form called > "+TemplateFrom" updating a new field called "Date/Time Field" using the > Remedy generated SQL View via a Filter. I was not able to get $TIMESTAMP$ > to resolve using ARUtilities so I had to build it using Remedy workflow. > > > > > > UPDATE A_TemplateForm > > SET Date_Time_Field = DATEDIFF(second, '1970-01-01 00:00:00', > '$TIMESTAMP$')+25200 > > WHERE Request_ID = '000000000000001' > > > > Adding 25200 is to account for my GMT offset. There are more elegant ways > to account for your offset. I just kept it simple for this proof of > concept. > > > > Jason > > > > On Mon, May 7, 2012 at 12:29 PM, Nancy Tietz <[email protected]> wrote: > > ** > > Wow – I just found out I can use the ‘SQL’ thing in ARUtilities to > actually SEE my Data!!! > > I’m so excited!!! > > > > Okay I’ll try to hold myself back on the ‘!’s and smileys… > > > > The C950000019 field is a new field that I added to hold the ‘opened’ > date-time for the incident. It is a ‘date/time’ type of field. > > > > I’m on SQL Server – so the ‘sp_help’ is great to know, since I’m an Oracle > person that got plopped into this BMC Remedy environment. (not very long > ago as you all can tell…) > > > > So here is my Update statement that I hope would work… however I am > unclear how to format the Timestamp now. > > So would I do an > > Integer($TIMESTAMP$) > > Or I’ll look it up somewhere… > > > > On Load of the window: > > UPDATE HPD_Help_Desk SET UM_Opened_By = $USER$, UM_Opened_Date = > $TIMESTAMP$ WHERE Incident_Number = $Incident Number$ AND > Incident_Number != $NULL$ AND UM_Opened_By = $NULL$; > > > > On Close of the window: > > UPDATE HPD_Help_Desk SET UM_Opened_By = $NULL$, UM_Opened_Date = $NULL$ > WHERE Incident_Number = $Incident Number$ AND $Incident Number$ != $NULL$ > AND UM_Opened_By = $USER$; > > > > Thanks everyone!! > > > > *From:* Action Request System discussion list(ARSList) [mailto: > [email protected]] *On Behalf Of *Jason Miller > *Sent:* Monday, May 07, 2012 3:19 PM > > > *To:* [email protected] > *Subject:* Re: to commit or not to commit... that is the question > > > > ** This is a dev system right? It appears you are getting a handle on a > few different techniques and concepts here. I bet there are probably a few > of us wincing while reading this thread. When working with Direct SQL your > gun is always loaded. Be careful where you point that thing and make sure > you always hit your target. > > > > That said we are absolutely here to help and you have caught our interest > :) > > > > One thing that stood out to me in the the UPDATE is "C950000019 = > $TIMESTAMP$." I am assuming C950000019 is a Remedy Date/Time field. > Setting that to $TIMESTAMP$ in Direct SQL is going to give you errors > because you'll be trying to put a character string into an Integer field. > If C950000019 is a Character field you are good to go (but Date/Time kind > of loses it's value when stored as text). To store $TIMESTAMP$ in an > Remedy Date/Time field you will need to convert it to UNIX Epoch > time (integer) before the UPDATE. > > > > Jason > > > > On Mon, May 7, 2012 at 11:58 AM, Joe Martin D'Souza <[email protected]> > wrote: > > Just to add to Jason, replace spaces & special characters with _. > > You should see all your columns in your DB table or view if you describe > (describe or desc) it.. > > eg: > Desc HPD_Help_Desk > Desc Tnnn (replace nnn with the schemaid as appropriate) > > This works on Oracle.. > > If you are using MS SQL or Sybase, describe will not work.. On MS-SQL & > sybase use: > sp_help HPD_Help_Desk > > Joe > > > > -----Original Message----- > From: Jlbess > Sent: Monday, May 07, 2012 2:46 PM Newsgroups: > public.remedy.arsystem.general > To: [email protected] > Subject: Re: to commit or not to commit... that is the question > > If you're going to use C id's instead of your field names, you need to > replace HPD_HELP_DESK with the T table. > The views like HPD_HELP_DESK don't have the C names, they have the > database name you gave your field. (replace spaces with underscore) > > You also need to put single quotes around '$USER$' and '$Incident Number$' > > > Jason > > On May 7, 2012, at 1:48 PM, smiley <[email protected]> wrote: > > Hi again - > > Here is my (hopefully) correct Direct Sql (which is an Action within my > Active Link): > > UPDATE HPD_Help_Desk SET C950000018 = $USER$, C950000019 = $TIMESTAMP$ > WHERE C1000000161 = $Incident Number$ > > The 'C' numbers are my Field ID's. I'm not sure if I'm supposed to > indicate > the form HPD:Help Desk with the view HPD_Help_Desk. > > A SQL using the same column names is not working at all in ARUtilities > > SQL, so ... I'm not sure how to test it. > Any ideas how to test this?? > > I'm used to Oracle dbs, not MS SQL Server. > I really appreciate it!!! > Thank you for your help!!! > > > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > > > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > > > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
