Translate Date to English Words

The Function to convert number to English words has been modified slightly to get th english words when date is given as an input.

Note: The function can still be optimized and some code can be reduced. This function is just a draft. When using this function in real time scenario some unused code has to be removed.


-- SQL user-defined function - UDF - SQL scalar-valued function - SQL Date to text

 

-- SQL user-defined function - UDF - SQL scalar-valued function - SQL Date to text

CREATE FUNCTION fnDateToWords(@date as varchar(20))

    RETURNS VARCHAR(1024)

AS

BEGIN

         DECLARE @DATE_CONVERTED DATETIME

         SET @DATE_CONVERTED = CAST(@date AS DATETIME)

         DECLARE @MonthName VARCHAR(100), @DAY INT, @MONTH INT, @YEAR INT

         SET @DAY = DAY(@DATE_CONVERTED)

         SET @YEAR = YEAR(@DATE_CONVERTED)

         SET @MONTH = MONTH(@DATE_CONVERTED)

         declare   @date_english varchar(1024)

      DECLARE @Below20 TABLE (ID int identity(0,1), Word varchar(32))

      DECLARE @Below100 TABLE (ID int identity(2,1), Word varchar(32))

              DECLARE @day_tbl TABLE (ID int identity(0,1), Word varchar(32))

      INSERT @Below20 (Word) VALUES

                        ( 'Zero'), ('One'),( 'Two' ), ( 'Three'),

                        ( 'Four' ), ( 'Five' ), ( 'Six' ), ( 'Seven' ),

                        ( 'Eight'), ( 'Nine'), ( 'Ten'), ( 'Eleven' ),

                        ( 'Twelve' ), ( 'Thirteen' ), ( 'Fourteen'),

                        ( 'Fifteen' ), ('Sixteen' ), ( 'Seventeen'),

                        ('Eighteen' ), ( 'Nineteen' )

       INSERT @Below100 VALUES ('Twenty'), ('Thirty'),('Forty'), ('Fifty'),

                               ('Sixty'), ('Seventy'), ('Eighty'), ('Ninety')

DECLARE @YEARNAME varchar(1024) =

(

  SELECT Case

    WHEN @YEAR = 0 THEN  ''

    WHEN @YEAR BETWEEN 1 AND 19

      THEN (SELECT Word FROM @Below20 WHERE ID=@YEAR)

   WHEN @YEAR BETWEEN 20 AND 99  

     THEN  (SELECT Word FROM @Below100 WHERE ID=@YEAR/10)+ '-' +

           dbo.fnNumberToWords( @YEAR % 10)

   WHEN @YEAR BETWEEN 100 AND 999  

     THEN  (dbo.fnNumberToWords( @YEAR / 100))+' Hundred '+

         dbo.fnNumberToWords( @YEAR % 100)

   WHEN @YEAR BETWEEN 1000 AND 999999  

     THEN  (dbo.fnNumberToWords( @YEAR / 1000))+' Thousand '+

         dbo.fnNumberToWords( @YEAR % 1000) 

          ELSE ' INVALID INPUT' END

)

SELECT @YEARNAME = RTRIM(@YEARNAME)

SELECT @YEARNAME = RTRIM(LEFT(@YEARNAME,len(@YEARNAME)-1))

                 WHERE RIGHT(@YEARNAME,1)='-'

Insert @day_tbl(Word) values

('Zero'), ('First'), ('Second'), ('Third'), ('Fourth'), ('Fifth'), ('Sixth'), ('Seventh'), ('Eighth'), ('Ninth'), ('Tenth'), ('Eleventh'), ('Thirteenth'), ('Fourteenth'), ('Fifteenth'),

('Sixteenth'), ('Seventeenth'), ('Eighteenth'), ('Ninteenth'), ('Twentieth')

DECLARE @DAYNAME VARCHAR(100)

=

(

       select case when @DAY = 30 THEN 'Thirtieth'

                     when @DAY = 31 then 'Thrity First'

                     when @day between 1 and 21

                        THEN (SELECT Word FROM @day_tbl WHERE ID=@day)

                     when @day between 21 and 30

                     then 'Tewenty '+ (SELECT Word FROM @day_tbl WHERE ID=@day)

                    

          ELSE ' INVALID DAY INPUT' END

       )

SELECT @MonthName  = DATENAME(month,@DATE_CONVERTED)

set @date_english = @DAYNAME + ' '+ @MonthName+', '+@YEARNAME

RETURN (@date_english)

END

GO

 /* Example

 SELECT DateInEnglish=dbo.fnDateToWords ('2012-01-02')

 */