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

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.

Oracle Goodness

I've spent years in SQL Land, and I consider myself fairly well informed about data. But after working with some of the people I have, I realize that there is so much that I don't know. I do know enough to do what I need to do without causing smoke and fire and other general mayhem, but just about everything I know now I've had to discover and learn myself. I've had a few good mentors that pointed out ways to tell if I'm writing slow, inefficient queries (and I've done plenty of that), and my views have shifted towards DB performance in my queries. I've also come to realize why software developers and DBAs often seem to be at odds with each other. Some of the worst queries I ever wrote were when I was writing in-code queries and didn't understand their impact. One of my previous databases had several related tables with 10s of millions of rows each. That's not BIG data in the modern sense, but that's A LOT of data. More than enough to let me easily see the difference between my code-based reporting query that ran in 2 hours and the DBA-optimized SQL that ran in 2 seconds. The SQL looked weird, but it was MUCH faster. Big lesson learned there. A database isn't just a place to store and retrieve data. You can get lots more from it by knowing how to ask nicely rather than trying to brute force the data you want. It's about getting SETS of data rather than simply trying to filter data. And it's about letting the database do the job it was made to do.

I like T-SQL, and I learned quite a bit about it in my reporting. And now that I'm back in Developer Land and making the move to an Oracle system, I'm seeing a few things I don't like about PL-SQL, but that's likely because I'm just not used to it. Only time will tell.

However, there are a few things I do like quite a bit. For instance, when making a new stored procedure (my current task), I used to have to do something like this:

IF OBJECT_ID('xxxxx') IS NULL EXEC('CREATE PROCEDURE xxxxx ...') GO 
ALTER PROCEDURE xxxxx ...

Or do several lines of logic with checking sproc existence in a system view. It wasn't hard, but it was long and involved.

MySQL was a bit better, but that wasn't my primary environment:

DROP PROCEDURE IF EXISTS xxxxx ;
CREATE PROCEDURE xxxxx ...

Though dropping and recreating db objects is never nice to existing permissions. :-/

With Oracle, I get to do:

CREATE OR REPLACE xxxxx ...

It's nice to have that kind of functionality already built in without having to worry about killing permissions. SO much easier.

Now, on to build some sprocs....