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
Why don’t you use VARCHAR_FORMAT? It converts timestamps into various varchar formats. This is a built in function that does everything you need.
The reverse to take time strings and convert into timestamp is TIEMESTAMP_FORMAT.
The functions don’t produce the ordinal suffixes to the day of month.
I am going to be honest, in that before people commenting on this and a previous post then I did not know this function existed, its not in any of the books that I have and it is not SEO’ed very well hint hint IBM. Equally as you have pointed out in your second post it does not have the st, nd, rd and th suffixes and therefore would not have worked for what I needed.