Thanks for the replies. Mark, I was hoping that I was missing something obvious and wouldn't have to do so much string manipulation but I may have to go that route. This is actually related to a previous issue I posted, my data feed is a daily XML file exported from the other system so the file is basically one giant string. For various reasons I don't have the option of web services at this time, I'm stuck with the XML flat file once per day.
I realized while composing a longer reply that there are two other steps in the process that could be causing this problem. The complete workflow is that I walk a table to pick up the field name and field ID on the display-only form where the data element needs to end up. There's a set fields that parses it out of the long XML string into a temp character field first, then a run process Application-Copy-Field-Value to copy it to the destination date/time field. This is the first place that I hadn't considered might be causing the problem because it only returns a 0/1 result for success/failure so I can't see what it is setting into the date/time field without putting a message in there to pause the workflow so I can see the transitory data. There is also a subsequent push fields action that could be causing the problem. Below is a set of log lines showing all the steps: walk the table to get the destination field name & ID and copy the data element from the XML to the temp field (action 0), then action 1 uses the run process to copy to the date/time field, then action 6 in a different active link pushes to a regular form. The problem could be introduced in action 1 or action 6. (I am walking a table instead of hard-coding because there are nearly 40 data fields to parse and I've already had one additional field added to the spec so it's easier to add a row to the supporting form instead of modifying workflow when a field is requested to be added.) <ACTL> 0: Set Fields <ACTL> Current Field ID (936870941) = 536871196 <ACTL> Current Field Name (936870940) = DiscoveredDate <ACTL> Current Field Value (936870936) = 2011-12-06 00:00:00.0 <ACTL> 1: Set Fields <ACTL> Process: Application-Copy-Field-Value 536871196 936870936 <ACTL> Integer Field (936870937) = 0 <ACTL> 6: Push Fields <ACTL> To Schema RegularForm on Server @ <ACTL> (536871196) = Wednesday, December 31, 1969 16:33:31 PM ___________________________ Rick Westbrock Support to SPAWAR - IT Service Management Project, Code 54520 QMX Support Services Office (619) 524-2303 From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Rick Cook Sent: Friday, May 10, 2013 12:26 PM To: arslist@ARSLIST.ORG Subject: Re: Converting date formats in set fields action ** Why would you need to parse or munge the data? The date/time data is stored as a string, and the client settings determine how it is displayed. So why would you need to do anything more than copy the field to a temp date/time field on the destination system, which would display it in the new format? Rick On May 10, 2013 10:35 AM, "Brittain, Mark" <mbritt...@navisite.com> wrote: Hi Rick, For a Friday this might work. Since the format is always the same maybe you can probably do a series of substring set fields in a filter to put in a temp date (character) field and then move to your final date field. incomingdate is also a character field. Set field tempdate SUBSTR($incomingdate$,0,3) result YYYY Set field tempdate SUBSTR($incomingdate$,8,9) + "/" + $tempfield$ result DD/YYYY Set field tempdate SUBSTR($incomingdate$,5,6) + "/" + $tempfield$ Result MM/DD/YYY Set field finaldatefield $tempdate$ Good Luck, Mark -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Rick Westbrock Sent: Friday, May 10, 2013 1:03 PM To: arslist@ARSLIST.ORG Subject: Converting date formats in set fields action I don't know if the long week has caught up with me and this is a heavy question for a Friday. I am just not able to figure out how to convert a date that I am getting from another system in YYYY-MM-DD format to the MM-DD-YYYY format that my server uses. For testing purposes I'm just pasting the output from the other system into a display-only form and then doing a set fields for the date portion of that string into my date/time field (DiscoveredDate). If my symptoms below make sense to anyone I would gladly take any advice on how to convert the date format. The date is actually coming in as YYYY-MM-DD HH:MM:SS with milliseconds but the time portion is all zeroes. I am using the LEFT function to set the value into a temp character field (zTmpDiscDate) taking only the first 10 characters (i.e. the date portion) which works fine. When I do a set fields (current screen) to take that value into my date/time field DiscoveredDate it sets it to a default of Wednesday, December 31, 1969 16:33:20 PM presumably because the incoming date format doesn't match. I tried using a set fields with a SQL statement as shown below but it just sets my temp character field zTmpDiscDate to 2000 for some reason: SELECT CONVERT(VARCHAR(10), $zTmpDiscDate$, 101) AS DiscDate The SQL log shows that it is parsing the date field but even with AL, Database and API logging turned on in the WUT this is all I get back which is not helping me figure out where the failure lies. The server is on Windows 2008 and the database is MS-SQL Server 2008. The ultimate goal of this is to process incoming data on the server without human intervention at all, I'm just using the WUT as a test platform to tweak my code. <SQL > <TID: 0000000336> <RPC ID: 0000258953> <Queue: List > <Client-RPC: 390620 > <USER: rwestbrock > <Overlay-Group: 1 > /* Fri May 10 2013 09:45:51.6090 */SELECT CONVERT(VARCHAR(10), 2012-09-03, 101) AS DiscDate<SQL > <TID: 0000000336> <RPC ID: 0000258953> <Queue: List > <Client-RPC: 390620 > <USER: rwestbrock > <Overlay-Group: 1 > /* Fri May 10 2013 09:45:51.6090 */OK<API > <TID: 0000000336> <RPC ID: 0000258953> <Queue: List > <Client-RPC: 390620 > <USER: rwestbrock > <Overlay-Group: 1 > /* Fri May 10 2013 09:45:51.6090 */-GSQL OK<CLAT> /* Fri May 10 2013 09:45:51.5820 */-API call <ACTL> zTmpDiscDate (936880920) = 2000 <ACTL> 8: Set Fields <ACTL> DiscoveredDate (536871196) = Wednesday, December 31, 1969 16:33:20 PM ___________________________ Rick Westbrock QMX Support Services ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years" This e-mail is the property of NaviSite, Inc. It is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential, or otherwise protected from disclosure. Distribution or copying of this e-mail, or the information contained herein, to anyone other than the intended recipient is prohibited. ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years" _ARSlist: "Where the Answers Are" and have been for 20 years_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
