Convert date string to date/time object

[BCinHOU]

I have a JDBC call to grab some data including a date/time stamp.  For the life of me, I can't figure out how to convert that string to a proper date/time field.  It's important because I need to use validation strings to present a more simplified date/time to users on a form.  But I'd like to keep the original date/time so I can reinsert it into a SQL db at the end.  I created a test process so I can watch the progress.

input string = '2010/08/18 16:41:23'

Expression = parse-dateTime-withFormat (/process_data/@datestring,"yyyy/MM/dd hh:mm:ss","en","US","WIN","CDT")

Result = August 18, 2010 9:41:23 PM GMT

Then I try to put that value into a date field and I get the error:
: Invalid ISO8601 DateTime:August 18, 2010 9:41:23 PM GMT:java.text.ParseException: Unparseable date: "August 18, 2010 9:41:23 PM GMT"

I've tried like 100 different variations to map into a date/time object with the same result.  I can use the parse-dateTime or parse-dateTime-withFormat functions all day long but I can't get that string value into a proper date field.  Any ideas?  Please help as I'm about to pull my hair out ...and that won't be pretty.

(Note:  I realize my time zone didn't come thru correct but I'm not concerned about that right now)

[_Nith_]

I think you have to format your date string as 2010-08-22T09:31:23Z  in order to assign it to a DateTime value.

Nith

[BCinHOU]

I finally figured that out but the part that throws me is the Z = zulu.  The time I'm pulling in is CDT so why do I want to format to zulu time?

What is interesting is I think I found a bug.  Later on in the process I insert this same data again (initially I retreive the data from a DB and then later insert it back to a different table).  If I use the FormatString function and designate CDT as the timezone, LiveCycle adds 5 hours.  If I retreive the time leaving the "Z" in there, then it returns CDT.  That, to me, is a bug.

The application should be smart enough to know what timezone the server is in and not alter the time if I specify the same timezone.  As such, I have to retreive the date without specifying the timezone and HOPE that CDT will always be correct.

LiveCycle appears to interpret CDT as the same thing as zulu.  So if I want to pull out a time in EST, I can't specify it explicity.  If I do it'll take CST + 4 ...not Zulu + 4 (or GMT +4).  So to compensate, I'd have to take GMT - 1 to get EST.  Even IF I did that, what happens during daylight savings time?

Curious if anybody else has run accross this ...or am I missing something?

[Adam_Chojnacki · Accepted solution]

To solve this problem I am using ExecuteScript activity with code:

import java.util.Date;
import java.text.SimpleDateFormat;

String dateString = patExecContext.getProcessDataStringValue( "/process_data/@input" );

SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = (Date)formatter.parse(dateString);

patExecContext.setProcessDataValue( "/process_data/result", date );

where

input - string process variable with string dates "2010-01-01 23:59:59"

date - date process variable