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"
