standardizeTimestamp - ColdFusion UDF: ParseDateTime is a black box and doesn't support all date/time strings

standardizeTimestamp.cfm

<!--- 2026-08-12 11:06 - standardizeTimestamp UDF - Because ParseDateTime is a black box and doesn't support all date/time strings. --->
<cfscript>
public string function standardizeTimestamp(required string timestamp, string timeZone="America/Los_Angeles") hint="I parse common date, time and timestamp strings (including ODBC literals and compact digit forms) and return them normalized to provided time zone as yyyy-mm-dd HH:nn:ss" {
	var input = trim(arguments.timestamp);
	var zoneId = createobject("java", "java.time.ZoneId").of(arguments.timeZone);
	var dtfClass = createobject("java", "java.time.format.DateTimeFormatter");
	var outFormatter = dtfClass.ofPattern("yyyy-MM-dd HH:mm:ss");
	var enLocale = createobject("java", "java.util.Locale").ENGLISH;
	var litType = "";
	var m = "";
	var p = "";
	var formatter = "";

	// 1. Unwrap ODBC literals: {ts '...'}, {d '...'}, {t '...'}
	m = refindnocase("^\{(ts|d|t)\s+'(.+)'\}$", input, 1, true);
	if (arraylen(m.pos) eq 3 && m.pos[1] gt 0) {
		litType = lcase(mid(input, m.pos[2], m.len[2]));
		input = trim(mid(input, m.pos[3], m.len[3]));
	}

	// 2. Normalize compact digit forms to ISO so no engine/JVM quirks apply
	if (refind("^\d{14}$", input)) {
		input = mid(input,1,4) & "-" & mid(input,5,2) & "-" & mid(input,7,2) & "T" & mid(input,9,2) & ":" & mid(input,11,2) & ":" & mid(input,13,2);
	} else if (refind("^\d{8}$", input) and litType neq "t") {
		input = mid(input,1,4) & "-" & mid(input,5,2) & "-" & mid(input,7,2);
	}

	// 3. RFC 1123: "Fri, 07 Aug 2026 17:55:43 +0000" and "... GMT"
	try {
		return createobject("java", "java.time.ZonedDateTime")
			.parse(javacast("string", input), dtfClass.RFC_1123_DATE_TIME)
			.withZoneSameInstant(zoneId).format(outFormatter);
	} catch (any e) {}

	// 4. ISO 8601 with offset or Z: "2026-08-07T10:55:43-07:00", "...T17:55:43.23Z"
	try {
		return createobject("java", "java.time.OffsetDateTime")
			.parse(javacast("string", input))
			.atZoneSameInstant(zoneId).format(outFormatter);
	} catch (any e) {}

	if (litType neq "t") {
		// 5. Local date/time, ISO style (handles "2026-08-07 10:55:43" and fractional seconds via T-swap)
		try {
			return createobject("java", "java.time.LocalDateTime")
				.parse(javacast("string", replace(input, " ", "T")))
				.atZone(zoneId).format(outFormatter);
		} catch (any e) {}

		// 6. Local date/time, common non-ISO patterns (assumed already US/Pacific; M/d/h accept 1-2 digits)
		for (p in ["yyyy-M-d H:mm", "M/d/yyyy H:mm:ss", "M/d/yyyy h:mm:ss a", "M/d/yyyy h:mm a", "M/d/yyyy H:mm", "MMMM d, yyyy h:mm:ss a", "MMMM d, yyyy h:mm a", "MMM d, yyyy h:mm:ss a", "MMM d, yyyy h:mm a", "d-MMM-yyyy H:mm:ss"]) {
			try {
				formatter = createobject("java", "java.time.format.DateTimeFormatterBuilder").init()
					.parseCaseInsensitive().appendPattern(p).toFormatter(enLocale);
				return createobject("java", "java.time.LocalDateTime")
					.parse(javacast("string", input), formatter)
					.atZone(zoneId).format(outFormatter);
			} catch (any e) {}
		}

		// 7. Date-only -> midnight US/Pacific (ISO first, then common patterns)
		try {
			return createobject("java", "java.time.LocalDate")
				.parse(javacast("string", input))
				.atStartOfDay(zoneId).format(outFormatter);
		} catch (any e) {}
		for (p in ["M/d/yyyy", "MMMM d, yyyy", "MMM d, yyyy", "d-MMM-yyyy"]) {
			try {
				formatter = createobject("java", "java.time.format.DateTimeFormatterBuilder").init()
					.parseCaseInsensitive().appendPattern(p).toFormatter(enLocale);
				return createobject("java", "java.time.LocalDate")
					.parse(javacast("string", input), formatter)
					.atStartOfDay(zoneId).format(outFormatter);
			} catch (any e) {}
		}
	}

	// 8. Time-only -> today's date in US/Pacific
	try {
		return createobject("java", "java.time.LocalTime")
			.parse(javacast("string", input))
			.atDate(createobject("java", "java.time.LocalDate").now(zoneId))
			.atZone(zoneId).format(outFormatter);
	} catch (any e) {}
	for (p in ["h:mm:ss a", "h:mm a", "H:mm"]) {
		try {
			formatter = createobject("java", "java.time.format.DateTimeFormatterBuilder").init()
				.parseCaseInsensitive().appendPattern(p).toFormatter(enLocale);
			return createobject("java", "java.time.LocalTime")
				.parse(javacast("string", input), formatter)
				.atDate(createobject("java", "java.time.LocalDate").now(zoneId))
				.atZone(zoneId).format(outFormatter);
		} catch (any e) {}
	}

	// throw(type = "StandardizeTimestamp.ParseError", message = "Unable to parse timestamp: #arguments.timestamp#");
	return datetimeformat(input, "iso");
}
</cfscript>

standardizeTimestamp_tests.cfm

<cfscript>
tests = [
	tostring("Fri, 07 Aug 2026 17:55:43 +0000")
	,tostring("Fri, 07 Aug 2026 17:55:43 GMT")
	,tostring("2026-08-07T10:55:43-07:00")
	,tostring("2026-08-07T10:55:43.123-07:00")
	,tostring("2026-08-07T17:55:43Z")
	,tostring("2026-08-07T17:55:43.23Z")
	,tostring("2026-08-07 10:55:43")
	,tostring("{ts '2026-08-07 10:55:43'}")
	,tostring("{d '2026-08-07'}")
	,tostring("{t '10:55:43'}")
	,tostring("2026-08-07")
	,tostring("08/07/2026 10:55:43")
	,tostring("08/07/2026 10:55 AM")
	,tostring("8/7/2026 10:55 AM")
	,tostring("8/7/2026 10:55 am")
	,tostring("08/07/2026")
	,tostring("8/7/2026")
	,tostring("August 7, 2026 10:55:43 AM")
	,tostring("august 7, 2026 10:55:43 am")
	,tostring("Aug 7, 2026 10:55 AM")
	,tostring("07-Aug-2026 10:55:43")
	,tostring("7-Aug-2026 10:55:43")
	,tostring("20260807105543")
	,tostring("20260807")
];

for (test in tests){
	data = [
		"t1_original": tostring(test)
	];
	data["t2_standardizeTimestamp"] = standardizeTimestamp(data.t1_original);
	try {
		data["t3_parsedatetime"] = parsedatetime(data.t1_original);
	} catch (any e){
		data["t3_parsedatetime"] = "CFERROR";
	}
	writedump(var=data, label="Result: #test#");
}
</cfscript>