There are 12 Chinese Zodiac characters. I took the most recent year for each character and figured out the modulo for each animal. From there, it’s a simple matching of sign modulo value to the modulo of the year being passed in. It’s not a strict interpretation of the Chinese Zodiac as I would have to take into account specific time ranges instead of just the years. Consider this more of an implementation of the version you’d find on a placemat in a Chinese Restaurant in America.
Create Function dbo.fnChineseZodiac ( @Year int) Returns varchar(12) As Begin Declare @Animal varchar(12); Declare @CZ Table (YearMod int not null primary key, Animal varchar(12) not null); Insert Into @CZ Values (0, 'Monkey'), (1, 'Rooster'), (2, 'Dog'), (3, 'Pig'), (4, 'Rat'), (5, 'Ox (Bull)'), (6, 'Tiger'), (7, 'Rabbit'), (8, 'Dragon'), (9, 'Snake'), (10, 'Horse'), (11, 'Sheep (Goat)'); Select @Animal = Animal From @CZ Where YearMod = @Year%12; Return @Animal; End
Select dbo.fnChineseZodiac (1970);
(1 row(s) affected)