Problem with Date & Time

[himerius]

Hello, the noob is back
I'we been trying to set up a date and time field from this:
200710311004
text field
to
2007-10-31T10:04:00Z
Date & Time with the following format
{CCYY-MM-DD}T{HH24:MM:SS[.0-6][+/-ZZ:ZZ]}

I really cant get this to work, its the Z that is my nemesis.

First i just changed the field to text, and use subtract, but now that i need it for different timezones, i need to have it in Date & Time subclass.

Can anyone help me solve this riddle?

[rep]

TODATETIME()

The TODATETIME function converts a text string of a specified format to a
date/time.

[himerius]

Does not work.
The problem is with the Z
The result is
2007-10-31T10:04:00
How do I add it. It is not in the source, so how do I add plaintext to datetime.

[rep]

So wait...you're trying to get the time zone out of an input field, that doesn't have the time zone in it? You may get a date in this input field that is a few days old, and you want to include the "ZZ:ZZ? Or is it always the current date, like, whatever date is in that field, plus the current ZZ:ZZ?

I've personaly never had a need to use the ZZ:ZZ, but I was thinking something like:

=FROMDATETIME(TODATETIME("200710311004"),"{CCYY-MM-DD}T{HH24:MM:SS[.0-6][+/-ZZZZ]}")

But I could get that to work.

good luck.

[rep]

So wait...you're trying to get the time zone out of an input field, that doesn't have the time zone in it? You may get a date in this input field that is a few days old, and you want to include the "ZZ:ZZ? Or is it always the current date, like, whatever date is in that field, plus the current ZZ:ZZ?

I've personaly never had a need to use the ZZ:ZZ, but I was thinking something like:

=FROMDATETIME(TODATETIME("200710311004"),"{CCYY-MM-DD}T{HH24:MM:SS[.0-6][+/-ZZZZ]}")

But I could get that to work.

good luck.

[himerius]

Yes, the input doesnt have a timezone, but the output field needs to have it. the date field is from when the document was created.
But as you can see on the output, its supposed to have only one Z, not
ZZ:ZZ

[himerius]

Hi.
Well, I did a workaround, with a submap, and changed the datetime to text..

Code: Select all

=left((text(addhours(todatetime(DateTime,"{CCYYMMDDHH24MM}"), 1))),4)+ "-"
+
mid((text(addhours(todatetime(DateTime,"{CCYYMMDDHH24MM}"), 1))),5,2)+ "-"
+
mid((text(addhours(todatetime(DateTime,"{CCYYMMDDHH24MM}"), 1))),7,2)+ "T"
+
mid((text(addhours(todatetime(DateTime,"{CCYYMMDDHH24MM}"), 1))),9,2)+ ":"
+
mid((text(addhours(todatetime(DateTime,"{CCYYMMDDHH24MM}"), 1))),11,2)+ ":00" + "Z"