if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_zodiac]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_zodiac]
GO
Create function dbo.udf_zodiac (@dob datetime)
returns varchar(15)
as
begin
declare @zodiac varchar(15)
declare @dob2 datetime
set @dob2= convert(datetime,convert(varchar(2),month(@dob))+'-'+convert(varchar(2),day(@dob))+'-2000' )
select @zodiac = case 
when @dob2 between '3/21/2000' and '4/20/2000' then 'Aries'
when @dob2 between '4/21/2000' and '5/21/2000' then 'Taurus'
when @dob2 between  '5/22/2000' and   '6/22/2000' then  'Gemini'
when @dob2 between  '6/23/2000' and  '7/23/2000' then 'Cancer' 
when @dob2 between  '7/24/2000' and  '8/23/2000' then 'Leo'
when @dob2 between  '8/24/2000' and  '9/23/2000' then 'Virgo'
when @dob2 between  '9/24/2000' and  '10/23/2000' then 'Libra'
when @dob2 between  '10/24/2000'and  '11/22/2000'then 'Scorpio'
when @dob2 between  '11/23/2000'and  '12/22/2000' then 'Sagittarius'
when @dob2 between  '12/23/2000'and  '1/19/2000' then 'Capricorn'
when @dob2 between  '1/20/2000' and  '2/19/2000' then 'Aquarius'
when @dob2 between  '2/20/2000' and  '3/20/2000'then  'Pisces' end
return @zodiac
end
go
select dbo.udf_zodiac('09-09-1971') as Zodiac
go
--result  Virgo
select dbo.udf_zodiac('10-28-1970') as Zodiac
go
--result Scorpio
select dbo.udf_zodiac('05-02-1945') as Zodiac
go
--result Taurus