Dates to “Long” – Friday function

Time for another Friday function.

The ask

This time I was asked by our marketing bods to come up with something that made the dates in the SQL that is merged into our marketing and informational emails (glorified mail merge) from a dd/mm/yyyy (13/06/2012) or yyyy-mm-dd (2012-06-13) to a ddth MMM YYYY so becomes 13th June 2012.

They had been trying to do this themselves, with some mixed and non standard ways of doing it, generally it just made the code really hard to debug when they had issues, personally I could not find anything in the IBM info centre or online to do this so I created them a UDF that takes in a date and supplies out a “long” format date.

DB2 Date to “Long” format – Code

CREATE FUNCTION GLOBAL.CONVERT_DATE_LONG (IN_DATE DATE)
DETERMINISTIC
NO EXTERNAL ACTION
RETURNS VARCHAR(30)
LANGUAGE SQL
BEGIN ATOMIC
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Takes Date in creates a long date
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Declare Vars
    DECLARE OUT_DATE VARCHAR(30);
    DECLARE DATE_PART VARCHAR(4);
    DECLARE MONTH_PART VARCHAR(20);
    DECLARE YEAR_PART  VARCHAR(4);

    IF(IN_DATE IS NOT NULL) THEN
        ----------------------------------------------------------------------------
        ----------------------------------------------------------------------------
        --Set some vars
            --Get the Base Parts
            SET DATE_PART = DAY(IN_DATE, 2);
            SET MONTH_PART = MONTHNAME(IN_DATE);
            SET YEAR_PART = YEAR(IN_DATE,4);

            --Remove the 0 from the front of the date part
            IF(DATE_PART IN ('01','02','03','04','05','06','07','08','09')) THEN
                SET DATE_PART = RIGHT(DATE_PART,1);
            END IF;

            --Put the th nd or rd in the DATE_PART
            IF(DATE_PART IN ('3','23')) THEN 
                SET DATE_PART = DATE_PART || 'rd';
            ELSEIF (DATE_PART IN ('2','22')) THEN
                SET DATE_PART = DATE_PART || 'nd';
            ELSEIF (DATE_PART IN ('1','21','31')) THEN
                SET DATE_PART = DATE_PART || 'st';
            ELSE 
                SET DATE_PART = DATE_PART || 'th';
            END IF;

        ----------------------------------------------------------------------------
        ----------------------------------------------------------------------------
        --Set and return the result    
        SET OUT_DATE = DATE_PART || ' ' || MONTH_PART || ' ' || YEAR_PART;

        RETURN OUT_DATE;
   ELSE
        RETURN NULL;
   END IF;

END

Queston?

Is it that there is not the function built into DB2 that does this or does everyone have something similar for the audience that they deal with?

Future Post

I will be looking at converting an integer that represents seconds into a time