Converting ISO Date/Time To ColdFusion Date/Time : Kinky Solutions : A Student's Perspective by Ben Nadel

Converting ISO Date/Time To ColdFusion Date/Time

Posted July 3, 2007 at 3:51 PM

I am working with some fun XML RPC (remote procedure call) stuff for a client when I had to convert the XML RPC standard ISO date/time stamp to a ColdFusion date/time stamp. I tried looking at CFLib.org, as I know nothing about ISO standards, but it seems the ones they have all expect the date to have dashes (ex. 1998-07-17T14:08:55). The problem is, the XML RPC date/time stamp does not have dashes:

19980717T14:08:55

It was easy to update the UDF to use a regular expression with optional dashes:

Launch code in new window » Download code as text file »

<cffunction
name="ISOToDateTime"
access="public"
returntype="string"
output="false"
hint="Converts an ISO 8601 date/time stamp with optional dashes to a ColdFusion date/time stamp.">
<cfargument
name="Date"
type="string"
required="true"
hint="ISO 8601 date/time stamp."
/>
<!---
When returning the converted date/time stamp,
allow for optional dashes.
--->
<cfreturn ARGUMENTS.Date.ReplaceFirst(
"^.*?(\d{4})-?(\d{2})-?(\d{2})T([\d:]+).*$",
"$1-$2-$3 $4"
) />
</cffunction>

Notice that the "-?" provide for optional dashes. Then, this can be called as such:

Launch code in new window » Download code as text file »

<!---
Output the standard ColdFusion date/time stamp
for this XML RPC standards compliant ISO 8601
date/time stamp.
--->
#ISOToDateTime( "19980717T14:08:55" )#

The above code will give us the proper ColdFusion date/time formatting:

1998-07-17 14:08:55

XML RPC is some fun stuff to work with. I know there are some libraries out there already, but I hope to release some sort of utility of mine own.

Download Code Snippet ZIP File

XStandard v2.0 Available: The Best WYSIWYG Editor Just Keeps Getting Better

Need Help Making A Viable ExpandServerPath() ColdFusion UDF

Adobe ColdFusion 8.0.1 Update - Helping Programmers To Be Signifanctly Less Girlie - Download ColdFusion 8 Update 8.0.1 Now.

Reader Comments

Ben, this returns a STRING that happens to look like a date. There's a slight semantical difference in that.

I recommend using parseDateTime() and use a returntype of date in your UDF.

--
Adam

Posted by Adam Cameron on Jul 3, 2007 at 6:10 PM

@Adam,

As far as I know, ColdFusion stores all simple values as strings and casts them on the fly when needed, which is why "3" is a date and so is "00A". But, I am interested in this ParseDateTime() method. I tried using it:

#ParseDateTime( "19980717T14:08:55" )#

And it is throwing the error:

"19980717T14:08:55" is not a valid date format.

I also tried:

#ParseDateTime( "19980717T14:08:55", "pop" )#

... but got a similar error. How does this function work?? Thanks.

Posted by Ben Nadel on Jul 3, 2007 at 6:17 PM

Hmm... I can see how this little function could prove handy when dealing with microformats. Great work, Ben!

Posted by Christian Ready on Jul 3, 2007 at 6:27 PM

@Ben
I think you would use ParseDateTime() / LSParseDateTime() on the replaced string ie:

Does that work?

Posted by Shuns on Jul 3, 2007 at 6:35 PM

i believe cf datetimes internally are offsets (decimal days since 31-dec-1899) though i have no idea if they are raw strings & converted on the fly.

parseDateTime or lsParseDateTime won't work as that string isn't like the default locale en_US formatting or any other locale i can recall. but then again neither is the datetime string returned by your function.

Posted by PaulH on Jul 4, 2007 at 2:36 AM

Ben, sorry, but you're mistaken. CF actually does it the other way around: it stores variables as specific data types, but will attempt to cast the data to a string when called for (like when going <cfoutput>#now()#<cfoutput>.

Run this sample code. Apologies if the output here is munged, but your blog won't let me submit the post with tags in it, which kinda sux on a CF-oriented blog! It also won't let me preview my post to see if using HTML-entities will work instead of angle-brackets.

<cfoutput>
<cfset i = int(0)>
int(0) 
#i.getClass()# 
Cast to String: #i#

<cfset s = "foo">
"foo" 
#s.getClass()# 
Cast to String: #s#

<cfset ts = now()>
now() 
#ts.getClass()# 
Cast to String: #ts#

<cfset d = createOdbcDate(now())>
createOdbcDate(now()) 
#d.getClass()# 
Cast to String: #d#

<cfset q = querynew("col")>
<cfset queryAddRow(q)>
<cfset querySetCell(q, "col", 1)>
q 
#q.getClass()# 
Won't automatically cast to a string 

q["col"] 
#q["col"].getClass()# 
Won't automatically cast to a string 

q.col 
#q.col.getClass()# 
Cast to String: #q.col# 

q.col[1] 
#q.col[1].getClass()# 
Cast to String: #q.col[1]#

<cfset x = xmlNew()>
<cfset x.xmlRoot = xmlElemNew(x, "foo")>
xmlNew() 
#x.getClass()# 
Cast to String: #x# (need to view-source to see it) 

xmlRoot 
#x.xmlRoot.getClass()# 
Cast to String: #x.xmlRoot# (need to view-source to see it) 

</cfoutput>

--
Adam

Posted by Adam Cameron on Jul 4, 2007 at 4:34 AM

Oh, and sorry: I didn't mean do the parseDateTime() on the INPUT string, I meant as a last step within your UDF, before returning the value. The yyyy-mm-dd HH:mm:ss format will reliably convert to a proper CF date/time object with parseDateTime().

--
Adam

Posted by Adam Cameron on Jul 4, 2007 at 4:39 AM

@Adam,

My apologies for the mistake. I had assumed that ColdFusion will convert string to particular data types when necessary. For instance, when I create the string:

I had assumed this was stored as a string and then converted to a date when date-manipulation was run on it? But from what it seems you are saying, it is actually creating a date and then converting it back to a string when I use it a string?

Similarly,

... is creating a number and then being cast back to a string when it is needed as a string??

This seems very wasteful. This makes ColdFusion have to guess that the data type before you ever have to use it. I think it would be more efficient to keep it as a string and then only cast it when necessary.

And, also keep in mind that I was specifically talking about Simple values... not sure why you threw in XML and Query objects :) I don't even know how ColdFusion would store them as a string and cast to complex types as necessary.

However, dumping out the Java class name for 34 and "34" both return String as their class. So, while date might be different, it looks like all numbers and strings are stored as strings. Although, I know that CreateTimeSpan() returns a Double (if memory serves correctly).

Honestly, I think we are splitting hairs here :)

Also, what tags where you trying to submit? As far as I know, my comments only block the A-tag?

Posted by Ben Nadel on Jul 4, 2007 at 5:31 PM

Hi Ben.
I'm not sure which tag it was: the error msg didn't say, it just said "you can't post that". Once I changed the left-angle-brackets in the code to < entities, it posted fine. It certainly didn't mind the angle-brackets in the CFOUTPUT tags at the top, which I did not think to "escape". Anyhow, using HTML entities works fine, so I'll do that.

Now, back to CF data types: you're not quite getting it.

When you do this:

<cfset dtToday = "07/04/2007" /> (*)

you are NOT creating a date. You're creating a string. Things wrapped in quotation marks get created as strings. Irrespective of what they might look like to a human.

Just because it looks like a date to you, doesn't mean it's a date. In the same way CF will automatically cast a date object to a string if it thinks that's best (like when you output it), CF will also cast a string to be a date if it seems fit. So if you were to use that string in a function that expects a date, you will not get an error: CF will work out what to do.

Well it kind of will, most of the time. And dates are the best example of when it can go wrong in CF to use the wrong date type for the job at hand. You see 4/7/2007 as July 4. I see it as April 7 (at least we can agree on the year). A computer will need to take a bit of a stab as to which way around it interprets that, educating its guess by looking @ the locale settings on the OS or applications involved. And computers will often cock it up, and get the dd and mm parts around the wrong way. You will see many posts on the Adobe forums in which people have this issue. It's usually when there are mismatched locales, or - perish the thought - the person is outside USA (who I think are the only ones with mm/dd/yy as their chosen short-format? Not sure).

So this is why one should shy away from using a string when the situation actually calls for a date: using the best tool for the job.

To create a date in CF, the easiest way is to do this:

This will create a date object which will never be handled ambiguously in date-oriented operations.

So - back to the original post - this is why I recommended parsing your string to a date, in a function which claims to be doing date conversions. In your case, as the string format is the unambiguous ISO format, one will never have issues using a stirng insetad of a date, as CF will always correctly cast it as a date. However "best practice" would suggest you should MAKE IT a date, if that's what it is. That's all.

More on the casting thing.

If one does this:

CF will create a string, not an integer. Why? Because CF is typeless (well: kinda. Sometimes. Except for when it isn't), and in the normal run of events - and if possible - it will create everything as strings to be as type-neutral as possible. This makes sense as usually CF is used for outputting strings of HTML.

This is why - to demonstrate my point about CF types - I used the int(0). int() returns a number (read the docs to confirm that). It is interesting - given the function name and purpose - it actually returns a double-precision float, rather than an actual integer. This is a bit crap on the part of CF, but so be it.

As for the queries and the XML in my example, I just wanted to offer examples of when the auto-casting DOESN'T occur (query); when it DOES occur, with other complex objects (XML); and does, but in possibly an unexpected or "unstable" way (query columns: two different results, depending on syntax).

I agree re the hair splitting, in all regards other than the date issue. Because it *is* a real issue, and it causes people grief. So when a date is called for... always *use one*.

Cheers.

--
Adam

(*) No need to close your CF tags btw. CFML is SGML, not XML.

Posted by Adam Cameron on Jul 4, 2007 at 6:50 PM

It didn't like your CFSET tag that I reproduced that time (and forgot to escape the opening angle bracket).

--
Adam

Posted by Adam Cameron on Jul 4, 2007 at 6:51 PM

I have some error! :(

Posted by High-technology on Jul 5, 2007 at 8:13 AM

What error?

Posted by Ben Nadel on Jul 5, 2007 at 8:55 AM

@Adam,

I see what you mean, and I think it makes a lot of sense. Actually, I do a lot of interfacing with the underlying Java APIs and I can tell you that it will throw data-type errors at seemingly random times. However, if you actually understand that not all simple values are actually strings, this starts to make a lot of sense.

I try to use JavaCast() for all numeric Java arguments, but I generally let the string arguments pass through as-is, assuming that ColdFusion will convert them to Java strings as needed. However, like I said before, ColdFusion is really just guessing at the conversion you want, and it certainly doesn't always work.

So, I guess what I am saying is, thanks for pointing out my misunderstanding about the data types and I agree, actually creating a date/time stamp as the return for this function would be the proper thing to do.

Sweeet!

Also, I will look into the CFSET tag restriction. I don't think it should be doing that (at least it was not intentional). What kind of computer are you on? Maybe the browser doesn't escape it the same way?

Posted by Ben Nadel on Jul 5, 2007 at 9:07 AM

Hi Ben.
I'm using WinXP Pro and Firefox 2.0.0.3.

Another consideration on this "escaping" issue... when I am returned to the data-entry form having had my post rejected, any escaping I had manually done (eg: using < entities instead of left-angle-bracket characters) has been "de-escaped"; eg the HTML entities are gone, and the actual characters are posted into the message area. I would expect my original, unmodified text to be in place.

This is not a problem at all, but given we're talking about it, perhaps worth mentioning.

--
Adam

Posted by Adam Cameron on Jul 5, 2007 at 4:43 PM

@Adam,

This is a headache dealing with the form inputs in general. If you look at the source code, you will see that your actual escaped text is in there. However, the textarea interprets it as the actual character. In order for me to re-display what you typed, I would have to actually escape the "&" before the "lt;". Same thing for quotes (well, really any escaped character I guess).

I guess then it becomes a decision: do I return exactly what you wrote (and have the textarea possibly change it)? Or, do I modify what you wrote so that the data will be different but the display will be the same... see what I mean.

I still need to look into it though, there is no reason it should block cfset. For instance, when I do:

I do not get blocked. Which is why I was hoping you were on a Mac or something (that I have not tested so much on).

Posted by Ben Nadel on Jul 5, 2007 at 4:49 PM

Hi Ben.
As a standard practice, you should be using htmlEditFormat() when putting a value in a form control, maybe?

--
Adam

Posted by Adam Cameron on Jul 5, 2007 at 5:23 PM

@Adam,

That could be.... and to be totally honest, I only recently found out that that ColdFusion method even existed :) But certainly that is a good idea. Is that something you do? I don't know what the best practice is here; I always just do something like this:

However, before the form is rendered, I usually do loop over the form fields to clean them up for output. This would be the perfect place to run this function.

Thanks for the tip.

Posted by Ben Nadel on Jul 5, 2007 at 5:30 PM

I rarely write code for UI type stuff, but when I do: yes, I htmlEditFormat() form values, if they originate from human input. It's a security issue to NOT do that.

--
Adam

Posted by Adam Cameron on Jul 6, 2007 at 3:31 PM

Ben,

First, let me say that your function is an excellent little parser that demonstrates the power of regex to find a pattern and reformat it. Given your post talks about two issues: XML-RPC and ISO-8601, I'd like to address them separately.

XML-RPC

The datetime data type in the XML-RPC specification is woefully inadequate and violates W3C Recommendation XML Schema, Part 2: Datatypes Second Edition (the standard for XML datatypes). The type of lexical truncation done to the datetime type is not allowed in Section D of the XML Schema. As such, the datatype is malformed.

The XML-RPC spec sidesteps the issue of internationalization completely:

"What timezone should be assumed for the dateTime.iso8601 type? UTC? localtime?

"Don't assume a timezone. It should be specified by the server in its documentation what assumptions it makes about timezones."

So when this malformed date-time value is passed to the data consumer, exactly what assumption should be made? I don't know the set up the sender has -- they have a database server in Chicago, have a web server hosted in San Jose, and my system is located in Toronto. Which timezone do I use?

The whole point of a standard is to ensure everyone follows the same protocol to avoid programming because everyone's "Doing their own thing." XML-RPC reintroduces date-time ambiguity simply because the authors' refuse to conform to the W3C Recommendation.

Personally, I won't touch XML-RPC because of issues like this. It may very well be implemented by thousands of machines world-wide, but 50,000 servers telling me the world is flat doesn't make them right.

CF uses SOAP to natively expose remote methods, and with good reason. SOAP pretty much conforms to the XML Schema. If push comes to shove, I'll still rely on good old WDDX to transfer data from one machines to another.

ISO-8601

There's good reason the W3C adopted this as its standard for date time data. It's unambiguous, non-ethnocentric, and lexically formats Gregorian dates and time-of-day data from largest to smallest unit of time, and handles time zones.

As your example function deals with parsing an ISO-8601 dateTime value into something ColdFusion can consume natively, my example to confines itself to the same task. However, it takes time zone data into account when parsing and returns the native data type -- that of the ColdFusion Datetime value.

Here's the function. It's been tested in CFMX 6.1 through CF8 and is currently used in production projects.

I didn't include all the error handling this function does through try-catch. The example assumes a valid date-time string is going in.

If you put in the value: "2007-11-20T12:00:00-05:00" (Noon Eastern Standard Time) into the function, you'll get back (if set to "utc") a date/time value of {ts '2007-06-14 17:00:00'}. If set to "local", it returns {ts '2007-11-20 12:00:00'}.

You may ask why this is important? Let's assume that I receive data from a server located in St. John's Newfoundland. I am in the Eastern Standard Time Zone. Newfoundland Standard Time is 3 hours and 30 minutes behind UTC. They've sent me the data at 12:00 noon their time.

Passing the string "2007-11-20T12:00:00-03:30" returns a result of {ts '2007-11-20 10:30:00'}. Newfoundland isn't the only jurisdiction in the world on a partial time zone value. There are others.

If I ran that value through your parser, I'd receive the value of "2007-11-20 12:00:00" and that is not the time it was sent either in my timezone nor in Co-ordinated Universal Time. It was sent at 2007-11-20 15:30:00 UTC, which is 2007-11-20 10:30:00 EST.

I hope you find this of interest, especially when using it to parse DateTime values from XML from disparate sources (other than XML-RPC).

Posted by A. Codemonkey on Nov 20, 2007 at 8:03 PM

@A. Codemonkey,

I find this stuff very fascinating. Unfortunately, I know almost nothing about internationalization. That's that this really is, right? Making an application make sense no matter what time zone you are in. My work has always just used the date on the ColdFusion server and that's it. To date, I haven't built any applications that depend on date/time stamps in any significant way.

This is, however, something that I think is very important and it is really something that I should be looking into.

Thanks for your insight into XML-RPC and into SOAP. I happen to think SOAP is a bit wordy when you are dealing with it manually, but so is XML-RPC. But, all things being equal, I would rather go with something that upholds a standard.

Your function is very cool. Again, I don't know much about actual ISO date time stamps, so looking at this is very helpful to see what is going on (or rather, what should be going on). Thanks!

Posted by Ben Nadel on Nov 21, 2007 at 7:17 AM

Date-time values are part of internationalization and ISO-8601:2000 was developed to create a singular standard format for dates and times.

The problem with XML-RPC, in part, is that its date-time format doesn't take into account differences in time-zone nor specify that all dates and times must be expressed in UTC. It simply says, "Make no assumptions about timezone," which a developer can't help but make.

Here's the link, btw, to the W3C Recommendation I cited (XML Schema Part 2: Datatypes Second Edition) Section D deals with the date-time datatype, it's format and what's acceptable or not.

http://www.w3.org/TR/xmlschema-2/

As for wordy, XML is always that, no matter what's being sent. :) What I truly love about it is for data exchanges, I don't have to worry about how your data model is structured. I only have to worry about mine. And most datatypes are somewhat universal.

Oh, and to throw in my two cents to the discussion about how some datatypes are are stored natively in CF. Date-times are stored in memory as a double-precision floating point number representing an offset from Dec 30, 1899 at 12:00 midnight. :)

The integer portion of the number is the number of days the current date is off of Dec. 30, 1899. The fractional (decimal) portion represents the fraction of days. Today (Nov. 22/07, 3:45 PM EST roughly) is 39408.6566550926. Negative offsets are used to handle date-times before 1899-12-30 00:00:00. :)

If you do the following, you'll see CF has no problems with figuring out the date:

<cfset nRightNow = 39408.6566550926>
<cfoutput>#DateFormat(nRightNow, "yyyy-mm-dd")# #TimeFormat(nRightNow, "h:mm:ss tt")#</cfoutput>

---

As for preferences for standards-compliant anything, I'm all for that. Following a standard makes my life way easier when I write code.

Posted by A. CodeMonkey on Nov 22, 2007 at 4:03 PM

@CodeMonkey,

Yeah, I happen to love the fact that ColdFusion stores dates as numbers (or at least that it easily can play with them). I find treating them as numbers to be a much faster, and often times a much less wordy, more clear means to manipulate them. Just have to be careful to use IsNumericDate() more often than IsDate().

Posted by Ben Nadel on Nov 25, 2007 at 4:24 PM

Post Comment | Ask Ben

Converting ISO Date/Time To ColdFusion Date/Time : Kinky Solutions : A Student's Perspective by Ben Nadel