Date Masking Inconsistency

What better way to celebrate Leap Day than with a Date Masking post? It's been cooking in my noggin a while and has been in some form of draft for a month, but better late than never, right? :-/

TL;DR: ALWAYS VERIFY YOUR DATE MASKS ARE DOING WHAT YOU THINK THEY'RE DOING!

I originally encountered this at the beginning of February and while digging for the cause, came across the issue in the CFDocs site (https://github.com/foundeo/cfdocs/issues/266). I played a bit with some of the masking and learned that some of my old ways were smacking me. I've been in the habit of masking a DateTime object with "MM/DD/YYYY..." or something like that. I absolutely hate date math and manipulation, but that hatred has led me to learning more than I ever wanted to know about the bazillion different epoch times and how 12:59:59.998 can sometimes be the same as 00:00:00.000. There's a bit of a rabbit hole there, and I've fallen down it more than I care for.

But I digress. The CFDocs site referenced an Adobe bug for CF10 (https://bugbase.adobe.com/index.cfm?event=bug&id=4105828) reported by Alexander Kwaschny, and as I was playing with this, I began to realize how big of an issue this actually could be for us. FORTUNATELY I didn't find anywhere in our code where we were using dateTimeFormat(), just dateFormat(), but we _were_ using the "YYYY" mask. I'm a firm believer in consistency (that may be a holdover from my piloting days), and I think that if you're going to do something, you should always try to do it the same way to minimize the opportunity for error. Date Masking is a big area that has a lot of potential to cause hard-to-detect problems, and we were setting ourselves up for a big one here. Though I can't really fault anyone, since pretty much every example of date masking or formatting that I've ever seen uses capital letters. I think it's insane for a language (THANKS JAVA, and maybe even ISO8601) to treat "Y" and "y" as two different things for such a narrow edge case. Until this issue, I had never even heard of a Week Year ("Y") (https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html). And to make the issue more fun, it would only even come into play at most about 6 days out of an entire year. You probably wouldn't even notice the problem.

So, my solution.... make sure my date masks reflect the proper things in the languages that I'm using. My years now are formatted with "yyyy" instead of "YYYY", though most of the time, it doesn't make a difference either way. Grrrr.....

======= MY RESEARCH ========
First, I wanted to verify that Oracle is documented to behave as I expect it to. https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#CDEHIFJA Oracle 11g Datetime Format Elements

Yup, Oracle treats "Y" and "y" as the same thing in its formatting. Note: even the Oracle examples show "Y" for the date mask.

One other cool thing I discovered about Oracle... the capitalization of the Month mask will determine the capitalization of the output.
'' Months : To_Char(myDate,'MONTH') AS CAPDATE, To_Char(myDate,'Month') AS Casedate, To_Char(myDate,'month') AS lowdate ''
I guess it's not something I've ever used nor paid attention to, but it's still kinda cool to know.

Now, back to ColdFusion:

For the Year difference, DateFormat() doesn't seem to have the problem, but DateTimeFormat() does. Interesting. And Lucee/Railo gets even weirder. http://trycf.com/gist/908f3b1ed6d7490bc470/acf11?theme=monokai

<cfscript>
myDate = CreateODBCDateTime('12/30/2015 12:34:56.789') ;
writeDump(dateFormat(myDate,'mm/dd/yyyy')) ;
writeDump(dateFormat(myDate,'mm/dd/YYYY')) ;
writeOutput('<br>') ;
writeDump(dateTimeFormat(myDate,'mm/dd/yyyy')) ;
writeDump(dateTimeFormat(myDate,'mm/dd/YYYY')) ;
</cfscript>

Will return "12/30/Y'Y'Y'Y" for the dateTimeFormat(), but the proper date for simple dateFormat(). Isn't that old pre-CFMX behavior?

The other mask that bugs me is "S" and "s". "s" is Seconds and "S" is milliseconds, except for when "l" or "L" are milliseconds. And, "S" returns Seconds in dateTimeFormat() and timeFormat(), where it returns the milliseconds in dateFormat(), which technically doesn't even specify a time component to return. ARGH!!!!

Once again, CONSISTENCY CONSISTENCY CONSISTENCY. And the case of a mask shouldn't affect its functionality. There's just too much room for a silent error.

Anyway, that's enough for tonight. I guess these things are semi-documented in various places, but I still don't like it. It just seems way too common to not be an issue. The Adobe ticket says the documentation in CF was fixed to be clearer. I'll check that. And I need to pull down CFDocs and change the date function masks to note these differences in behavior. But this post became the rabit hole that I warned about earlier.


===== OTHER NOTES ========================================================

CF > DateFormat() vs DateTimeFormat()
DateFormat -
 9 = http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7ff4.html
 10+ = https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-c-d/DateFormat.html

TimeFormat - http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6de0.html
DateTimeFormat - CF10+ - https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-c-d/DateTimeFormat.html
 "The function also follows Java date time mask. For more information, refer to Date and Time Patterns topic in SimpleDateFormat Java API page."

--------------------------

<cfscript>
myDate = CreateODBCDateTime('12/30/2015 15:16:17.19') ;
writeDump(dateFormat(myDate,'s')) ; // 17
writeDump(dateFormat(myDate,'S')) ; // 190
writeDump(timeFormat(myDate,'s')) ; // 17
writeDump(timeFormat(myDate,'S')) ; // 17
writeOutput('<br>') ;
writeDump(dateTimeFormat(myDate,'mm/dd/yyyy hh:nn:ss.l')) ; // 12/30/2015 03:16:17.190
writeDump(dateTimeFormat(myDate,'mm/dd/YYYY HH:NN:SS.L')) ; // 12/30/2016 15:16:17.190
writeOutput('<br>') ;
writeDump(datePart('yyyy',myDate)); // 2015
writeDump(datePart('YYYY',myDate)); // 2015
writeOutput('<br>') ;
writeDump(dateAdd('yyyy',1,myDate)); // {ts '2016-12-30 15:16:17'}
writeDump(dateAdd('YYYY',1,myDate)); // {ts '2016-12-30 15:16:17'}
</cfscript>

>>>>>>>>>>>>

http://trycf.com/gist/5a4209466ccf5e5ea854/acf11?theme=monokai
http://trycf.com/gist/ef686d4c4bf45fb85619/acf11?theme=monokai
http://trycf.com/gist/b24b9c91e6cee5e3c12e/acf?theme=monokai
http://trycf.com/gist/9db92000bb204a561edd/acf?theme=monokai

Links:
Original CFDocs Issue: https://github.com/foundeo/cfdocs/issues/266
http://www.sql-server-helper.com/tips/date-formats.aspx
http://www.java2s.com/Tutorial/Java/0040__Data-Type/SimpleDateFormat.htm
https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-by-category/date-and-time-functions.html
https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-c-d/DatePart.html
http://www.petefreitag.com/cheatsheets/coldfusion/dateformat/
https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-c-d/DateFormat.html