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?