Showing posts with label How to convert date of birth in words in SQL Server?. Show all posts
Showing posts with label How to convert date of birth in words in SQL Server?. Show all posts

How to convert date of birth in words in SQL Server?

Step 1: Below is the query that we can use in order to convert the date into words:

declare @date datetime = '01-10-1985'--Input DateOfBirth

declare @yr smallint = datepart(year, @date);
declare @month smallint= datepart(month, @date);
declare @day smallint = datepart(day, @date);
declare @word varchar(2000)

declare @NumTable table(NumId int, Name varchar(50))

insert into @NumTable
values(1,'First'),
(2,'Second'),(3,'Third'),(4, 'Fourth'),(5,'Five'), (6,'Six'),
(7,'Seventh'),(8,'Eighth'),(9,'Nineth'),(10,'Ten'),(11,'Eleven'),
(12,'Twelve'),(13,'Thirteen'),(14,'Fourteen'),(15,'Fifteen'),(16,'Sixteen'),
(17,'Seventeen'),(18,'Eighteen'),(19,'Nineteen'), (20,'Twenty'),(21,'TwentyFirst'),
(22,'Twenty Second'),(23,'Twenty Third'),(24,'Twenty Fourth'),(25,'Twenty Five'),
(26,'Twenty Six'), (27,'Twenty Seven'),(28,'Twnety Eight'),(29, 'Twenty Nine'),
(30,'Thirty'),(31, 'Thirty First'),(32,'Thrity Two'),(33,'Thrity three'),(34,'Thirty Four'),
(35,'Thrity Five'),(36,'Thiry Six'),(37,'Thirty Seven'),(38,'thrity eight'),(39,'Thirty Nine'),(40,'Forty'),
(41,'Forty one'), (42,'Forty two'),(43,'Forty three'),(44,'Forty four'),(45,'Forty Five'),(46,'Forty Six'),(47,'Forty Seven'),
(48,'Forty eight'),(49,'Forty nine'),(50,'fifty'),(51,'Fifty one'),(51,'Fifty one'),(52,'Fifty two'),(53,'Fifty three'),
(54,'Fifty four'),(55,'Fifty five'),(56,'Fifty six'),(57,'fifty seven'),(58,'fifty eight'),(59,'fifty nine'),
(60,'Sixty'),(61,'sixty one'),(62,'sixty two'),(63,'sixty three'),(64,'sixty four'),(65,'sixty five'),(66,'sixty six'),(67,'sixty seven'),
(68,'sixty eight'),(69,'sixty nine'),(70,'seventy'),(71,'seventy one'),(72, 'seventy two'),(73,'seventy three'),
(74,'seventy four'),(75,'seventy five'),(76,'seventy six'),(77,'seventy seven'),(78,'seventy eight'),(79,'seventy nine'),
(80,'eighty'),(81,'eighty one'),(82,'eighty two'),(83,'eighty three'),(84,'eighty four'),(85,'eight five'),(86,'eight six'),
(87,'eight seven'),(88,'eighty eight'),(89,'eighty nine'),(90,'ninety'),(91,'ninety one'),(91,'ninety one'),
(92,'ninety two'),(93,'ninety three'),(94,'ninety four'),(95,'ninety four'),(95,'ninety five'),(96,'ninety six'),
(97,'ninety seven'),(98,'ninety eight'),(99,'ninety nine')

-- Day
select @word = name
from @NumTable where NumId = @day

set @word = @word + ' '
-- Month
select @word = @word + DATENAME(month,@date)

set @word = @word + ' '

-- Year
select @word = @word + name
from @NumTable where NumId = left(@yr,2)

set @word = @word + ' '

select @word = @word + name
from @NumTable where NumId = Right(@yr,2)

select @word as 'DOB_in_Words'

Step 2: The output is as shown below:

How to convert date of birth in words in SQL Server?

Followers

Popular Posts