Friday, March 27, 2009

MySQL Missing Function DayOfWeekAsString()

MySQL has so much functionality, it's hard not to love.

But there are rare occasions when I wonder why they missed some simple function like DayOfWeekAsString()

Without it I have to manually to this all over the place whenever I need it:

ELT(DOWnumber,'Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday')b


Dear MySQL,

Please all this function natively.

Sincerely,

Your devoted developer.

Tuesday, March 17, 2009

Optional Parameters within CFScript Functions

This stumped me for a long time. How to make parameters optional in a cfscript function?

Tangent: I rarely use CFScript. It's an optional way to write encapsulated Coldfusion code. Some love it, some hate it, some couldn't live without it, some can't find a use for it and wonder why it was invented. Me? I'm neutral. There are clearly times when CFScript will greately reduce the lines of code, simplify functionality, and increase code readability. That's when I love it - Except when I wanted to use it but couldn't do what I could otherwise do with tagged markup. For example:

I wanted to achieve this MySQL function in Coldfusion:

SELECT STR_TO_DATE('200442 Monday', '%X%V %W');

Here is a function that I wrote in CFScript

    // This returns the Sunday DATE of the Week of the year passed in.
    // Last line of code is also in 'sys_query_projects.cfc' method 'weekrep'. ***
    // ADDED: second optional parameter, dayofweek number for the return date.
    function YWtoDate(yw) {
        var year = LEFT(yw,4);
        var week = INT(RIGHT(yw,2));
        var dow = 2;    //default to Monday
        var retdate = NOW();
        if (LEN(yw) NEQ 6 OR
yw NEQ INT(VAL(yw))) {
            retdate = DateFormat(NOW(),'yyyy-mm-dd');
        } else {
            retdate = DateFormat(DateAdd('ww',(week-1),DateAdd('d',IIF(DayOfWeek('#year#-01-01') IS 1,0,8-DayOfWeek('#year#-01-01')),'#year#-01-01')),'yyyy-mm-dd');
        }
        // Check the Day of the Week passed in - if any
        IF(ArrayLen(Arguments) GTE 2 AND INT(VAL(Arguments[2])) GTE 1 AND INT(VAL(Arguments[2])) LTE 7) {
            dow = INT(VAL(Arguments[2]));
        }
        return DateAdd('d',dow-1,retdate);  //returns the proper day of week requested
    }



Optional parameters in tagged markup is easy

<cfargument name="dow" required="No" type="any" default="2">

The other easy think about tagged markup is that the ORDER pf passed parameters does not matter. (Which is a terribly bad habit and runs against all other programming language conventionality, but is super convenient with you want to pass in only the second and fourth of five non-required parameters.)

Then I stumbled across this little gem called the Arguments[] structure. I never knew it existed! This opens a whole new ball game within CFScript Functions. It puts CFScript on an equal level with CFFunction. I believe CFFuntion has the same structure and a better way to catch any passed parameters, but somehow it never made it through my skull that functions within CFScript would have this. Order still maters and you can validate the incoming data however you please.