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

ColdFusion Scope Precedence

A while back (August 2015), Himansu Sekhar Khuntia logged a bug for ColdFusion 10 that dealt with the scope precedence of LOCAL and ARGUMENTS scopes within a function, and specifically how unscoped variables were effected with CF's scope hunting. Current behavior of ColdFusion 10 has ARGUMENTS taking precedence over LOCAL, and I really don't know how far back this problem goes, but apparently the behavior should be LOCAL over ARGUMENTS.

Original bug report for CF10: https://bugbase.adobe.com/index.cfm?event=bug&id=4031746
Possibly related for CF2016: https://bugbase.adobe.com/index.cfm?event=bug&id=4119653

Inside a function, LOCAL scope SHOULD take precedence over ARGUMENTS scope. This is fixed in 2016 (need to try it out) and planned to be fixed in 10/11. We'll see which patch it makes it into.

To make things interesting, I decided to play with the function scopes in CF10 (pre-scope order fix), and got some interesting results. See below.

Moral of the Story: ALWAYS SCOPE YOUR VARIABLES!!!!! To be extra safe, it probably wouldn't be a bad idea to try to keep from naming a variable the same in multiple scopes. ARGUMENTS and LOCAL are SUPPOSED to be mutually exclusive, but they aren't always.

Anyway, thank you to Adobe for actually clarifying that. Now I need to start reporting the discrepancy when I see it documented incorrectly, ie http://www.learncfinaweek.com/week1/Scopes/.

========================================================================

Where Did I Come From?

It's a little late, but since my sidebar gadget text became a bit verbose, I chose to put a quick "Who Am I?" over here. Or rather "Where Did I Come From?"

In a nutshell, I'm a plane jockey turned code monkey. I've been fascinated with computers since I was young. I taught myself BASIC on a Commodore 64 (which I still have) with Family Computing magazine and learned that you could double the space on your floppy disk with a hole punch. My first exposure to "networked" computing involved CompuServe and a modem that was only a couple of generations past a phone cradle. If you don't know what a handshake sounds like, you can't fully appreciate the speed jump from 300 to 12oo baud, much less the "blazing" speed of a 56K modem.

Even though I initially didn't follow my love of computing to a career, through an accidental twist of fate, I left my airline search and ventured onto my coding path. I was working with Access databases and writing basic HTML/CSS/JS back around 1997. In late 1999, I took some time off from trying to figure out what kind of airline pilot I wanted to be, and I went to work for my brother as a ColdFusion developer. I haven't looked back.

In that time, I've done quite a bit of different things. I've installed and repaired computers. I've set up small company and home networks. I've had nightmares about wiring Cat-5 cable and can still remember the wire order (even though I haven't had to do it in almost a decade). I've illustrated and laid out magazines, billboards and even a map of an airport ramp. I configured and sold tablet computers to pilots before they were even called "tablets", and once helped a guy troubleshoot his computer while he was flying at 45,000 ft over the ocean. I've written software for tablets, for the Web, for business reporting and even a good bit of SQL. I've spent the last several years of my life immersed in highly sensitive personal background, medical and military data. I spent several years as a SQL developer, but I'm back to being a web developer.

When I moved to Nashville, TN in 2005, I became a member of my first technology user group, the Nashville ColdFusion User Group (mostly because we hosted the group and I helped with setup and teardown). I met some brilliant and talented people though that group. Sadly, the NCFUG quieted down and went dormant for a while, until a few years ago when my friend Brett rebooted the group. He asked me to be his co-manager (even though I was mostly a SQL guy at that time), and I agreed. He passed away in early 2015, and I took over the group. Since then, I've come back over to ColdFusion development, and made a move out of state. I left the group in some very capable hands, but I still have a desire to see a resurgence of the group that I was a part of for so long.

I'll be the first person to admit that I'm not the fastest coder in the world. But when my stuff goes out, it doesn't come back. I pride myself on a fairly low bug-rate. I hate writing code more than once, and I tend to be hard on my earlier self. I've looked at code I wrote a few years ago and wondered a) who wrote this crap and b) after realizing that I did, how does it still work. 

Being a self-taught code monkey, I know there are more than a few holes in the things I think I know. I've learned a lot, but there are always more things to learn and plenty of ways to do things better. Fortunately (though this blog tends to prove that I procrastinate more than I'd like) I enjoy learning. I enjoy reading. I know a lot of stuff about a lot of stuff. I just hope it's useful stuff to know. The current world is a fascinating place. Between RaspberryPi, IoT, <insertJavascriptFlavorOfTheMonthHere> and all of the other things I'd like to do, I wish there was more time for all of it. :-/

I'll likely eventually move this vanity page to its own spot, but for now it gets to live as a blog post. 

ColdFusion 2016 Has Officially Dropped!

I meant to post this a few days ago when it actually happened, but I'm slacking again. The newest iteration of Adobe's ColdFusion has been released! They went away from their previous numbering scheme: this isn't ColdFusion 12, this is ColdFusion 2016. Interesting. And I'm not sure I like it much. By 2017, we'll already sound out-of-date. Can you imagine what CF2012 (CF10) or CF2014 (CF11) would sound like? And CF10 is still a HUGE part of the ColdFusion landscape. Besides, we really need a more positive impression of ColdFusion.

Anyway, I doubt work will get to this new version any time soon, and I'm kinda glad for that. I can already see a lot of things that will need to be refactored to function correctly. Hopefully some of these issues are on the roadmap to be fixed.

The safe navigation operator (?.) is kinda cool, and a built-in CLI is interesting (though I don't yet know how I'd use it). And moving the default install for CFIDE and the CFAdmin is LONG overdue. Hopefully that will cut down on some of the "security problems" that resulted from bad installations and not locking down servers.

I'll play with this one at home some more when I make some time to get a VM for it up and running again.

Here are some announcement links:

http://blogs.coldfusion.com/post.cfm/announcing-the-launch-of-the-newest-version-of-coldfusion-adobe-coldfusion-2016-release

https://helpx.adobe.com/coldfusion/whats-new.html
https://helpx.adobe.com/coldfusion/release-note/coldfusion-2016-release-notes.html

Oracle Number Formatting

This may be something you all already know, but it had me scratching my head for a minute. On the one hand, Oracle’s analytic functions like LAST_VALUE make some things kinda cool, but then Oracle smacks me with not handling number masking like pretty much every other language.

Even if you specify a decimal place mask, if the number ends with a 0 in the decimal, it will drop it.

Example:
1) To_Number(44.50,’99.99’) >> 45.5
2) To_Number(100/10.0,’99.99’) >> 10  <<< This one even drops the decimal and returns an integer 10, which leads to a whole different philosophical discussion about the differences between 10 and 10.00.

To get the decimal places mask that we want in Oracle, we have to use To_Char(44.50,’99.99’). But then that value is a string, so you won’t be able to do math on it. Except in ColdFusion, where it’s a numberish string. So to actually format it, we have to remask it in whatever code we use to output the value – ie in ColdFusion = NumberFormat(44.5,’99.90’) >> 44.50 and NumberFormat(100/10.0,’99.00’) >> 10.00

Add this to ColdFusion’s method of determining how to parse a value into JSON, and it makes things super fun. :-/

Moral of my story: If you need a number to be in a specific  format, don’t assume the database will give it back to you the way you thought you told it to.

Happy Brettuary!

This past March, Brett "BSmuv" Davis passed away. I've known him for almost 10 years. He was very dedicated to the coding community, and he worked hard to bring back the Nashville ColdFusion User Group. He brought me along to help. Brett had heaps of charm. He was funny. He loved a good office prank. He was larger than life itself. He was an inspiration to me. He was my friend.

I started working with Brett in September of 2007, and when his birthday began to roll around in February of 2008, he quickly made it clear that a single day of celebration wasn't enough. So he declared his birthday to be the month of Brettuary. Every year that I've known him, even after I no longer worked with him, I always wished him good cheer on his birthday. I'm a few days early this year, but I'd still like to offer my customary greeting. 

Happy Brettuary!

And Bonus Points if you can guess one of the highlights of Brett's life.

State of the CF Union Survey 2016

http://www.teratech.com/blog/index.cfm/2016/1/20/State-of-the-CF-Union-Survey-2016

Follow the link and take the survey.