Tag Archives: Colum

Can’t Believe DB2 does not have this function – Friendly Dates

June 13, 2012 8:16 pm / Leave a Comment /

So our marketing gurus at where I work needed a english friendlier format (thanks @_PeUR on the description of my last post via twitter) for the text in the emails that they send out, personally I would be happy with a dd/mm/yyyy or yyyy-mm-dd but suppose it is what you are used too. Our place is a little unusual as the marketers are allowed access to the data to design their own SQL sets to create the mailing lists. I have seen all sorts of SQL to do this some good some bad, but mostly it makes the SQL harder to read for no added functionality that i sometimes have to debug. Therefore I decided to create a function so that it at least can now just be an inline function. This function takes in an ISO or compatible with the DATE data type and returns a long data ddth MMM YYYY so 13/06/2012 becomes 20th June 2012.

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')) 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


Is it that I cant believe that there is not the function or does everyone have something similar for the audience that they deal with most often expect to see their date or time formats in?

Posted in: Data types, Date, DAY, DB2, DB2 Administration, DB2 Built in commands, DB2 built in functions, IBM, MONTHNAME, User Defined Functions, YEAR / Tagged: , , , ,

DB2 RAND()

February 23, 2012 1:52 pm / Leave a Comment /

Not done this for a while with moving house and looks like it will still be difficult with BT messing up the telephone lines in the building, but O2 are providing no end of support.  So thought I would do a short post in my lunch hour at work (actually taking a “break” for once) and do a short post.

One of the MySQL DBA colleagues asked me the other day if I could do a random update on a table, apart from the fact I could not understand at the time why you would want to randomly update a number of rows and not have an idea what you have done till you interrogate the data again, that’s another article for the what if I am allowed to publish it (doesn’t that sound intriguing!). The main IBM page in the DB2 Information Centre is here on the RAND() OLAP function.

So what’s the best way to update say 1000 rows in a 10000 row table randomly in DB2, I am not going to cover the way to do it on MySQL but heres how you could do it:

UPDATE <SCHEMA>.<TABLE> ST
SET <COLUMN> = SOMEVALUE
WHERE ST.<IDENTIFIER COLUM> = (SELECT <IDENTIFIER COLUM>
                              FROM <SCHEMA>.<TABLE>
                              ORDER BY RAND()
                              FETCH FIRST 1000 ROWS ONLY)

This code will randomly update 1000 rows, and will choose a different 1000 each time. You could but RAND() to other uses to create a random number but you will need to remember that it only generates a number between 1 and 0

So that’s it, one of my shorter ones.

Posted in: DB2, DB2 Administration, DB2 Built in commands, DB2 built in functions, DB2 Ecosystem, DB2 Maintenance, IBM, RAND, Uncategorized / Tagged: , , ,