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" > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
