16 Sep

MySQL Function to Convert Date To Words

Recently I saw a MySQL Stored Function requirement on Experts-Exchange for converting date into some specific words format.
You may find MySQL function for date to words conversion online; even udfs might be ready, but I decided to write my own.
I wrote this simple function mainly based on SELECT CASE to convert dates in to words as follows:

mysql>SELECT date_to_words(‘2010-05-08′);
Eighth Day of May Two Thousand Ten

Download sql file below the code.


DROP FUNCTION IF EXISTS `date_to_words` $$
CREATE FUNCTION `date_to_words` (mydate DATE) RETURNS VARCHAR(100)

/* Converts date into words */

DECLARE dateval INT;
DECLARE thousand INT;
DECLARE hundred INT;

DECLARE tensword VARCHAR(10);
DECLARE onesword VARCHAR(10);
DECLARE thousandsword VARCHAR(20);
DECLARE hundredsword VARCHAR(20);
DECLARE datevalsword VARCHAR(20);

SET yr=year(mydate);
SET dateval=day(mydate);


WHEN 1 THEN ‘First’
WHEN 2 THEN ‘Second’
WHEN 3 THEN ‘Third’
WHEN 4 THEN ‘Fourth’
WHEN 5 THEN ‘Fifth’
WHEN 6 THEN ‘Sixth’
WHEN 7 THEN ‘Seventh’
WHEN 8 THEN ‘Eighth’
WHEN 9 THEN ‘Ninth’
WHEN 10 THEN ‘Tenth’
WHEN 11 THEN ‘Eleventh’
WHEN 12 THEN ‘Twelfth’
WHEN 13 THEN ‘Thirteenth’
WHEN 14 THEN ‘Fourteenth’
WHEN 15 THEN ‘Fifteenth’
WHEN 16 THEN ‘Sixteenth’
WHEN 17 THEN ‘Seventeenth’
WHEN 18 THEN ‘Eighteenth’
WHEN 19 THEN ‘Nineteenth’
WHEN 20 THEN ‘Twentieth’
WHEN 21 THEN ‘Twenty-first’
WHEN 22 THEN ‘Twenty-second’
WHEN 23 THEN ‘Twenty-third’
WHEN 24 THEN ‘Twenty-fourth’
WHEN 25 THEN ‘Twenty-fifth’
WHEN 26 THEN ‘Twenty-sixth’
WHEN 27 THEN ‘Twenty-seventh’
WHEN 28 THEN ‘Twenty-eighth’
WHEN 29 THEN ‘Twenty-ninth’
WHEN 30 THEN ‘Thirtieth’
WHEN 31 THEN ‘Thirty-first’
END into datevalsword;

set thousand=floor(yr/1000) ;
set yr = yr – thousand * 1000;
set hundred = floor(yr / 100);
set yr = yr – hundred * 100;

IF (yr > 19) THEN
set tens = floor(yr / 10);
set yr = yr mod 10;
set tens=0;

SELECT CASE thousand
WHEN 3 THEN ‘Three’
WHEN 4 THEN ‘Four’
WHEN 5 THEN ‘Five’
WHEN 7 THEN ‘Seven’
WHEN 8 THEN ‘Eight’
WHEN 9 THEN ‘Nine’
END INTO thousandsword;
SET thousandsword=concat(thousandsword,’ Thousand ‘);

WHEN 0 then ”
WHEN 3 THEN ‘Three’
WHEN 4 THEN ‘Four’
WHEN 5 THEN ‘Five’
WHEN 7 THEN ‘Seven’
WHEN 8 THEN ‘Eight’
WHEN 9 THEN ‘Nine’
END INTO hundredsword;
if (hundredsword<>”) then
SET hundredsword=concat(hundredsword,’ Hundred ‘) ;
set hundredsword=”;
end if;

WHEN 2 THEN ‘Twenty’
WHEN 3 THEN ‘Thirty’
WHEN 4 THEN ‘Fourty’
WHEN 5 THEN ‘Fifty’
WHEN 6 THEN ‘Sixty’
WHEN 7 THEN ‘Seventy’
WHEN 8 THEN ‘Eigthy’
WHEN 9 THEN ‘Ninety’
END INTO tensword;

WHEN 3 THEN ‘Three’
WHEN 4 THEN ‘Four’
WHEN 5 THEN ‘Five’
WHEN 7 THEN ‘Seven’
WHEN 8 THEN ‘Eight’
WHEN 9 THEN ‘Nine’
WHEN 10 THEN ‘Ten’
WHEN 11 THEN ‘Eleven’
WHEN 12 THEN ‘Twelve’
WHEN 13 THEN ‘Thirteen’
WHEN 14 THEN ‘Fourteen’
WHEN 15 THEN ‘Fifteen’
WHEN 16 THEN ‘Sixteen’
WHEN 17 THEN ‘Seventeen’
WHEN 18 THEN ‘Eighteen’
WHEN 19 THEN ‘Nineteen’
END into onesword;

return concat(datevalsword, ‘ Day of ‘, date_format(mydate,’%M’),’ ‘,thousandsword,hundredsword, tensword,’ ‘,onesword);
END $$


Download SQL Code for converting date to words.date_to_words.sql

27 Aug

Computer usage and maintenance-my 10 practices

I have already written a post for my list of must have softwares. I’ve now decided to share how I use my machine.
In this post I will post my usage and maintenance methods for personal computer. I hope this post will help you in getting great usage experience after following it.

I’m a PC Freak! I like my computer to be fast, quick and handy. I don’t much like to use mouse and love to finish as many things possible by Keyboard only. If you’re not already following, I recommend these regular computer practices.

1. I Use Shortcuts

I have shortcuts for all my regularly used tools. For example my regular shortcuts are:

Putty: Ctrl+Alt+P
Notepad: Ctrl+Alt+N
Winamp: Ctrl+Alt+M
Editplus: Ctrl+Alt+Num+
Query Brower: Ctrl+Alt+Q
WinSCP: Ctrl+Alt+V
Wordweb: Ctrl+Alt+W
Paint: Ctrl+Alt+Num1
Word: Ctrl+Alt+Num2
Excel: Ctrl+Alt+Num3
Gtalk: Ctrl+Alt+G
Firefox: Ctrl+Alt+F
Chrome: Ctrl+Alt+C
Epic: Ctrl+Alt+E

It’s quite simple to create shortcuts like this.

Start > Program > your-program > executable (Shortcut) > right click > Properties > Specify Shortcut

I think below picture will explain it better.

Create Shortcut

2. I Clean My Desktop

Normally my machine has a black background / “beautiful” picture (you cannot take your eyes off) with no desktop icons :)
To remove all desktop icons I do:
Right Click on Desktop > Arrange Icons By > Uncheck Show Desktop Icon
Check Image:

3. I Use More Shortcuts

There are many basic and regularly used shortcuts I use regularly:
Minimize all windows: Flag+M
Switch between windows: Alt+Tab
Switch reverse: Alt+Shift+Tab (I think this is little less known)
On Windows Vista / Win7 system you get more animated Flat+Tab buttons.

… and I use even more shortcuts… seems like I need to dedicate one post for shortcuts :)


4. I Do Save Power and Follow Security

When ever I go away, I lock (Ctrl+L) my machine and turn my monitor off. Well, it saves power too :)


Right-click the desktop > click Properties.
In the Display Properties dialog box > click the Screen Saver tab > click the Power button.

You can set your power options respectively.

5. I Hate Automatic Updates

I make sure my windows automatic update is turned off.
Go to System Properties [Flag+Break] > Automatic Updates > Turn off Automatic Updates.

6. I Get Back to Work Quickly-Hibernate

I do also enable Hibernate. Hibernation will save your current session in memory and shuts your computer. But it provides instant startup :)
Start > Control Panel > Power Options Properties > Hibernete Tab > Check Enable Hibernate.
See Snap:

setting hibernate

7. I Don’t Start Unnecessary Things

Remove unnecessary startup:
Start > Run [Flag+R]
Type “msconfig” and press enter. This will open ‘System Configuration Utility’.
On ‘Startup’ tab I uncheck unnecessary services.

8.  I Check And Stop Unnecessary Services

Also I make sure no unnecessary service consume my machine’s memory by disabling them. Eg. I Disable ‘Automatic Updates’.
Start > Run [Flag+R]

9. I Don’t System Restore

System Restore is something that stores changes to my machine and enables restore it to particular point. But I disable it. It saves memory and risk of restoration of Viruses, in case. ;)

10. I Defragment

I also defragment my drives approx once a month.
Start > Run [Flag+R]

Do respond with your suggestions and review for my practices.

25 Aug

Shutter Speed & F-stop: Sony DSC HX1 Photography

Yesterday I was traveling from Ahmedabad to Vadodara and luckily I carried my DSC HX1 with me. I was even lucky that I was incapable of driving and was on front seat. No doubt my hand and car vibrations helped a bit in getting those designs. :)

I decided experiment with shutter speed for my Digital Camera and got some nice lightening decorations.

I was in Manual shooting mode; for most of the pics below I’ve used f-value (Aperature diameter) F8.0 and exposure is +2.0 unless specified otherwise. Minimum shutter speed I could set was NR 30″.

ISO is something I have not dared to play with till now, so I let my DSC HX1 use it Auto.

Okay so here I go with few of my shutter speed controlled light drawings :)

Right, a lot of vibrations caused those bumps… Further I decreased it to 30 Sec and I got:

Well, below is my steady shot (no Vibrations) for 10 second near Vadodara Toll Booth.

Suddenly I realised the full moon and changed focus to sky along with Exposure time 1/1250 and then 1/2000 Sec respectively.

Btw check above results with my previous capture [F/5.2, Exposure Time 1/30 Sec].

07 Aug

My list of must have free softwares-2010

As soon as I got a pc I start looking at Configuration, Connectivity [;)] and Installations. As for humans Food, Air, Water are basic necessities; for my computer I always make sure I do have perticular softwares installed. In this post I will list my 7 most used free softwares for general usage.


Of-course my English vocabulary is not that strong and I constantly need to find meanings and all. Hence I always make sure I have WordWeb. It is a one-click English thesaurus and dictionary for Windows that can look up words in almost any program.
It can also look up words in web references such as the Wikipedia encyclopedia.
To find meanings and search word: Select a word and press Ctrl+Alt+W OR simply Ctrl+Right click the word


This editor comes with a lot of features especially my favorites RegExp capabilities. It is a text editor, HTML editor, PHP editor, Java editor and Hex Viewer for Windows.
EditPlus also offers: Syntax highlighting for HTML, PHP, Java, C/C++, CSS, ASP, Perl and Seamless Web browser for previewing HTML pages and Uploading local files to FTP server and and more.


I barely use Internet Explorer. I believe Internet is not meant to be used by Internet-Explorer and vice versa. :)
My choice of browsers were Firefox and then I used Google Chrome a lot. But recently I’ve been using Epic Browser and I’m quite satisfied with the browsing experiance.


VLC Player:

For whom Music is ( / part of) life, a good player is very important. Winamp no doubt the best choice for playing mp3s. But you should have VLC Player as “It plays everything!”.

And what is more it can even play back the file or media if it is damaged! Missing or broken pieces are no stop to VLC, it plays all the video and audio information that’s still intact. You can also broadcast through it.


Computer games is something I cannot resist. My friends better know how much important it is to have a Virtual Drive in computer.
I choose DAEMON Tools, an advanced application for multiprotection emulation. It emulates max. 4 DVD-ROM drives and works with CD/DVD images created by many burning programs.
It supports:
cue/bin,iso,ccd (CloneCD),bwt (Blindwrite),mds (Media Descriptor File),cdi (Discjuggler),nrg (Nero),pdi (Instant CD/DVD),b5t(BlindWrite 5),b6t,isz.

Thanks Daemon you supported me for my NFS, FIFA, SOF, EOA, MUMMY, MAXPAINE… virtual disks :)


PDF Converter is a software which I use many of the times. DOPdf is the free PDF converter for both commercial and personal use which I use. It’s installed as a Virtual Printer which generates PDF when given a print. To create a PDF we just have to open document / image > choose Print > Select doPDF as printer and Specify file location for PDF.

Are you away from your home? I have something very important there and you need it? You need to guide some one or give a demo of something? Ay work related to Remote Access and Support over the Internet I choose TeamViewer.

TeamViewer connects to any PC or server around the world within a few seconds. You can remote control your partner’s PC as if you were sitting right in front of it.I mainly used it for downloads and guiding my parents using computer.

04 Aug

My worst mathematical program in perl

I have a Mathe-crap-matical friend. Who keeps digging into graves of dead formulas and equations and all possible Math related stuffs.
Since I started understanding Mathematics (that it’s not my cup of tea), I tried to stay away from it.

Now, that enthu creature mailed me his requirement. He says he “eat mathematics”, I prefer to stay away from such guys. But as a good friend I read his mails.

His requirement was to create a program. His requirements are:

N is a natural number falling in range [1 – 999999]
X is a number given by user with range [6000 – 20000]

You have to write a program such that you find a number N whose square root or cube root is such that the first digit of X comes in the Xth decimal place and second digit of X comes in the (X+1)th decimal place and so on…

e.g. user enters 12…
then the number N would be such that CubeRoot(N) or SqRoot(N) = dddd.ddddddddddd12

You may write program in any language.

Check screen shot to understand what it does [note the name]:

maths program

maths program

Alright, I understood the dooms day is closing in… Did you notice the decimal places correction he was asking? 6000!! Damn…

Any ways, I chose perl and accomplished the task. I’m not sure how and why he need this thing but still I hope it rested his soul. :)

This post is for people like me who have such math-e-manic friends.
You may download my script my_mathematics_pgm.pl.

print "Enter X:";
my $X = <STDIN>;
my $N=1;
while ($N < 100) {

	$PosAfterDesimal=index ($SQRT_VAL,'.');
	$PosOfXInSQRT =index ($SQRT_VAL, $X,$PosAfterDesimal);
	$PosOfXInSQRT = $PosOfXInSQRT -1;
	$PosAfterDesimal=index ($CUBERT_VAL,'.');
	$PosOfXInCUBERT =index ($CUBERT_VAL, $X, $PosAfterDesimal);

#	print $N . "->" . $SQRT_VAL . "->" . $X . "->> " .trim($PosOfXInSQRT). "\n";
	if (trim($PosOfXInSQRT) == $X) {
		print $X. " matched SQRT of " . $N . " at " . $X . "th Position \n" ;
		print "\t Where, N=" . $N . ", SQRT VAL=" . $SQRT_VAL . ", X=" . $X . "\n";
	} elsif (trim($PosOfXInCUBERT) == $X) {
		print $X. " matched Cube Root of " . $N . " at " . $X . "th Position \n";
		print "\t Where, N=" . $N . ", CUBEROOT VAL=" . $CUBERT_VAL . ", X=" . $X . "\n";
sub trim($)
        my $string = shift;

        if ( $string eq undef || $string eq "" )
                return "";
        $string =~ s/^\s+//;
        $string =~ s/\s+$//;

        return $string;
}#trim Ends
-- Kedar Vaijanapurkar --