I am converting UTC date into Oracle-friendly date as this:

<cfset x = #dateTimeFormat(lsParseDateTime(DateConvert("UTC2Local", "2021-12-09T15:11:32.273Z")), 'MM/DD/YYYY hh:nn:ss')#>

<cfoutput>#x#</cfoutput>

and getting this: 12/08/2021 111132 How do I convert back to "*2021-12-09T151132.273Z*" format?

First things first, separate the concepts of "a string that looks like a date" and "a date" in your mind. • this returns a date:

lsParseDateTime(DateConvert("UTC2Local", "2021-12-09T15:11:32.273Z"))

. This is what you generally want to have in your code. •

dateTimeFormat

takes a date and converts it into a string. You only ever really want to do that at the very last moment when the date value exits your app (html, json, some DBs, etc)

First, "DD" is the day of year, not the day of month. I'm sure you wanted "dd" instead. Next, the original milliseconds (.273) are gone and you can't get those back from the formatted value because it doesn't contain them. So, the best you can do is format what you DO have into what you want it to look like so something like... dateTimeFormat(x, "yyyy-mm-dd HHnnss.LLL").replace(" ", "T") & "Z"

just a note, in general, be careful with DD vs dd, lucee and acf 2021 treat DD as day in year. I suspect the challenge is around text mixed in the formatting. dateTimeFormat(x, "yyyy-mm-dd'T'HHnnss.lll'Z'"), especially when it conflicts with formats

facepalm... TIL that wrapping T or Z in single quotes is how you escape them in a date/time mask. Thanks for that.

I only just found that out too (not via here... googled some blog article)

Thanks for replies. I am trying to generate API call. The example they have is using CURL:

Although the escaping is clearly documented here: https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html @Mark Takata (Adobe) / @saghosh should really have that in the docs here though: https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-c-d/DateTimeFormat.html I have submitted an addition for the equiv on cfdocs

#dateTimeFormat(DateConvert("UTC2Local", return_date),'MM/DD/YYYY hh:nn:ss')#

and

#dateTimeFormat(lsParseDateTime(DateConvert("UTC2Local", "2021-12-09T15:11:32.273Z")), 'MM/DD/YYYY hh:nn:ss')#

returning the same result

Why don't you just pass the date? The DB driver already knows how to handle date objects. You don't need to convert them to strings.

I have no issues inserting dates into database. I am asking about generating dates "from database" in UTC format so I can pass them into API call (see above)

Yeah sorry, your wording was vague with the "and pass it into the query param". But yeah I see your HTTP call now, got it

when I try to reverse it as

<cfset utcNow = #dateConvert("Local2UTC", now())#>

I am getting {ts '2022-04-19 183148'}, there is no time zone