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"
