ColdFusion - DateTime Format with GMT offset

Question

I am using ColdFusion 10 to make some REST calls and the date returned is using a GMT offset.

Example: 2013-03-25T14:30:40-04:00

I need this formatted for 2 purposes:

1. Screen Display so it looks something like mm/dd/yyyy hh:mm:ss
2. To Insert into mySQL.

I have tried a variety of the CF time/date functions but continue to get the "is not a valid date format"

I thought maybe the #ParseDateTime(i.submitted_at,"pop")# would handle it with POP but same issue.

Spent a few hours now trying multiple variations and googling around now just going in circles. Any ideas would be greatly appreciated.

Thanks!

Answer 1

Have a look at the UDF DateConvertISO8601() on CFLib.

DateConvertISO8601(ISO8601dateString, targetZoneOffset) on CFLib

Description:
This function take a string that holds a date in ISO 8601 and converts it to ODBC datetime, but could be adapted to convert to whatever you like. It also will convert to a datetime in a timezone of your choice by specifying the offset, i.e. it could take a datetime in GMT and convert to PT. See http://www.w3.org/TR/NOTE-datetime for description of ISO 8601, the International Standard for the representation of dates and times.

Return Values:
Returns a datetime.

The source code is viewable at the link I provided.