Page 1 of 1
Problem with Date & Time
Posted: Thu Nov 15, 2007 3:00 am
by 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?
Posted: Thu Nov 15, 2007 8:03 am
by rep
TODATETIME()
The TODATETIME function converts a text string of a specified format to a
date/time.
Posted: Thu Nov 15, 2007 8:30 am
by 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.
..
Posted: Thu Nov 15, 2007 5:07 pm
by 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.
..
Posted: Thu Nov 15, 2007 5:07 pm
by 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.
Posted: Fri Nov 16, 2007 1:21 am
by 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
Posted: Fri Nov 16, 2007 4:00 am
by 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"