Recently I saw a MySQL Stored Function requirement on Experts-Exchange for converting date into some specific words format.
\nYou may find MySQL function for date to words conversion online; even udfs might be ready, but I decided to write my own.
\nI wrote this simple function mainly based on SELECT CASE to convert dates in to words as follows:<\/p>\n
mysql>SELECT date_to_words(‘2010-05-08’);
\nEighth Day of May Two Thousand Ten<\/p>\n
Download sql file below the code.<\/p>\n
DELIMITER $$<\/p>\n
DROP FUNCTION IF EXISTS `date_to_words` $$
\nCREATE FUNCTION `date_to_words` (mydate DATE) RETURNS VARCHAR(100)
\nDETERMINISTIC
\nBEGIN<\/p>\n\/* Converts date into words *\/<\/p>\n
DECLARE yr INT;
\nDECLARE dateval INT;
\nDECLARE thousand INT;
\nDECLARE hundred INT;
\nDECLARE tens INT;<\/p>\nDECLARE tensword VARCHAR(10);
\nDECLARE onesword VARCHAR(10);
\nDECLARE thousandsword VARCHAR(20);
\nDECLARE hundredsword VARCHAR(20);
\nDECLARE datevalsword VARCHAR(20);<\/p>\nSET yr=year(mydate);
\nSET dateval=day(mydate);<\/p>\n\/* DAY TO WORDS *\/<\/p>\n
SELECT CASE dateval
\nWHEN 1 THEN ‘First’
\nWHEN 2 THEN ‘Second’
\nWHEN 3 THEN ‘Third’
\nWHEN 4 THEN ‘Fourth’
\nWHEN 5 THEN ‘Fifth’
\nWHEN 6 THEN ‘Sixth’
\nWHEN 7 THEN ‘Seventh’
\nWHEN 8 THEN ‘Eighth’
\nWHEN 9 THEN ‘Ninth’
\nWHEN 10 THEN ‘Tenth’
\nWHEN 11 THEN ‘Eleventh’
\nWHEN 12 THEN ‘Twelfth’
\nWHEN 13 THEN ‘Thirteenth’
\nWHEN 14 THEN ‘Fourteenth’
\nWHEN 15 THEN ‘Fifteenth’
\nWHEN 16 THEN ‘Sixteenth’
\nWHEN 17 THEN ‘Seventeenth’
\nWHEN 18 THEN ‘Eighteenth’
\nWHEN 19 THEN ‘Nineteenth’
\nWHEN 20 THEN ‘Twentieth’
\nWHEN 21 THEN ‘Twenty-first’
\nWHEN 22 THEN ‘Twenty-second’
\nWHEN 23 THEN ‘Twenty-third’
\nWHEN 24 THEN ‘Twenty-fourth’
\nWHEN 25 THEN ‘Twenty-fifth’
\nWHEN 26 THEN ‘Twenty-sixth’
\nWHEN 27 THEN ‘Twenty-seventh’
\nWHEN 28 THEN ‘Twenty-eighth’
\nWHEN 29 THEN ‘Twenty-ninth’
\nWHEN 30 THEN ‘Thirtieth’
\nWHEN 31 THEN ‘Thirty-first’
\nEND into datevalsword;<\/p>\n\/* YEAR TO WORDS *\/
\nset thousand=floor(yr\/1000) ;
\nset yr = yr – thousand * 1000;
\nset hundred = floor(yr \/ 100);
\nset yr = yr – hundred * 100;<\/p>\nIF (yr > 19) THEN
\nset tens = floor(yr \/ 10);
\nset yr = yr mod 10;
\nELSE
\nset tens=0;
\nEND IF;<\/p>\nSELECT CASE thousand
\nWHEN 1 THEN ‘One’
\nWHEN 2 THEN ‘Two’
\nWHEN 3 THEN ‘Three’
\nWHEN 4 THEN ‘Four’
\nWHEN 5 THEN ‘Five’
\nWHEN 6 THEN ‘Six’
\nWHEN 7 THEN ‘Seven’
\nWHEN 8 THEN ‘Eight’
\nWHEN 9 THEN ‘Nine’
\nEND INTO thousandsword;
\nSET thousandsword=concat(thousandsword,’ Thousand ‘);<\/p>\nSELECT CASE hundred
\nWHEN 0 then ”
\nWHEN 1 THEN ‘One’
\nWHEN 2 THEN ‘Two’
\nWHEN 3 THEN ‘Three’
\nWHEN 4 THEN ‘Four’
\nWHEN 5 THEN ‘Five’
\nWHEN 6 THEN ‘Six’
\nWHEN 7 THEN ‘Seven’
\nWHEN 8 THEN ‘Eight’
\nWHEN 9 THEN ‘Nine’
\nEND INTO hundredsword;
\nif (hundredsword<>”) then
\nSET hundredsword=concat(hundredsword,’ Hundred ‘) ;
\nelse
\nset hundredsword=”;
\nend if;<\/p>\n\/*TENS To WORDS*\/
\nSELECT CASE tens
\nWHEN 2 THEN ‘Twenty’
\nWHEN 3 THEN ‘Thirty’
\nWHEN 4 THEN ‘Fourty’
\nWHEN 5 THEN ‘Fifty’
\nWHEN 6 THEN ‘Sixty’
\nWHEN 7 THEN ‘Seventy’
\nWHEN 8 THEN ‘Eigthy’
\nWHEN 9 THEN ‘Ninety’
\nELSE ”
\nEND INTO tensword;<\/p>\n\/*ONES To WORDS*\/
\nSELECT CASE yr
\nWHEN 0 THEN ”
\nWHEN 1 THEN ‘One’
\nWHEN 2 THEN ‘Two’
\nWHEN 3 THEN ‘Three’
\nWHEN 4 THEN ‘Four’
\nWHEN 5 THEN ‘Five’
\nWHEN 6 THEN ‘Six’
\nWHEN 7 THEN ‘Seven’
\nWHEN 8 THEN ‘Eight’
\nWHEN 9 THEN ‘Nine’
\nWHEN 10 THEN ‘Ten’
\nWHEN 11 THEN ‘Eleven’
\nWHEN 12 THEN ‘Twelve’
\nWHEN 13 THEN ‘Thirteen’
\nWHEN 14 THEN ‘Fourteen’
\nWHEN 15 THEN ‘Fifteen’
\nWHEN 16 THEN ‘Sixteen’
\nWHEN 17 THEN ‘Seventeen’
\nWHEN 18 THEN ‘Eighteen’
\nWHEN 19 THEN ‘Nineteen’
\nEND into onesword;<\/p>\nreturn concat(datevalsword, ‘ Day of ‘, date_format(mydate,’%M’),’ ‘,thousandsword,hundredsword, tensword,’ ‘,onesword);
\nEND $$<\/p>\nDELIMITER ;<\/p><\/blockquote>\n