问将日期时间转换为生肖名称

EN

CASE 
    WHEN MONTH(DateOfBirth) * 100 + DAY(DateOfBirth) BETWEEN 0120 AND 0219 THEN 'Aquarius'
    WHEN MONTH(DateOfBirth) * 100 + DAY(DateOfBirth) BETWEEN 0220 AND 0320 THEN 'Pisces'
    WHEN month(DateOfBirth) * 100 + DAY(DateOfBirth) BETWEEN 0321 AND 0419 THEN 'Ares'
    WHEN MONTH(DateOfBirth) * 100 + DAY(DateOfBirth) BETWEEN 0420 AND 0520 THEN 'Taurus'
    WHEN MONTH(DateOfBirth) * 100 + DAY(DateOfBirth) BETWEEN 0521 AND 0620 THEN 'Gemini'
    WHEN MONTH(DateOfBirth) * 100 + DAY(DateOfBirth) BETWEEN 0621 AND 0722 THEN 'Cancer'
    WHEN MONTH(DateOfBirth) * 100 + DAY(DateOfBirth) BETWEEN 0723 AND 0822 THEN 'Leo'
    WHEN MONTH(DateOfBirth) * 100 + DAY(DateOfBirth) BETWEEN 0823 AND 0922 THEN 'Virgo'
    WHEN MONTH(DateOfBirth) * 100 + DAY(DateOfBirth) BETWEEN 0923 AND 1022 THEN 'Libra'
    WHEN MONTH(DateOfBirth) * 100 + DAY(DateOfBirth) BETWEEN 1023 AND 1121 THEN 'Scorpio'
    WHEN MONTH(DateOfBirth) * 100 + DAY(DateOfBirth) BETWEEN 1122 AND 1221 THEN 'Sagittarius'
    ELSE 'Capricorn'
END

DECLARE @temp TABLE(DateOfBirth DATETIME);
INSERT INTO @temp VALUES ('19800517');

SELECT 
    DateOfBirth,
    CASE 
        WHEN DATEADD(YEAR,-DATEDIFF(YEAR,'19000101',DateOfBirth),DateOfBirth) BETWEEN CAST('19000321' AS DATETIME) AND CAST('19000419' AS DATETIME) THEN 'Ares'
        WHEN DATEADD(YEAR,-DATEDIFF(YEAR,'19000101',DateOfBirth),DateOfBirth) BETWEEN CAST('19000420' AS DATETIME) AND CAST('19000520' AS DATETIME) THEN 'Taurus'
        WHEN DATEADD(YEAR,-DATEDIFF(YEAR,'19000101',DateOfBirth),DateOfBirth) BETWEEN CAST('19000521' AS DATETIME) AND CAST('19000620' AS DATETIME) THEN 'Gemini'
        WHEN DATEADD(YEAR,-DATEDIFF(YEAR,'19000101',DateOfBirth),DateOfBirth) BETWEEN CAST('19000621' AS DATETIME) AND CAST('19000722' AS DATETIME) THEN 'Cancer'
        WHEN DATEADD(YEAR,-DATEDIFF(YEAR,'19000101',DateOfBirth),DateOfBirth) BETWEEN CAST('19000723' AS DATETIME) AND CAST('19000822' AS DATETIME) THEN 'Leo'
        WHEN DATEADD(YEAR,-DATEDIFF(YEAR,'19000101',DateOfBirth),DateOfBirth) BETWEEN CAST('19000823' AS DATETIME) AND CAST('19000922' AS DATETIME) THEN 'Virgo'
        WHEN DATEADD(YEAR,-DATEDIFF(YEAR,'19000101',DateOfBirth),DateOfBirth) BETWEEN CAST('19000923' AS DATETIME) AND CAST('19001022' AS DATETIME) THEN 'Libra'
        WHEN DATEADD(YEAR,-DATEDIFF(YEAR,'19000101',DateOfBirth),DateOfBirth) BETWEEN CAST('19001023' AS DATETIME) AND CAST('19001121' AS DATETIME) THEN 'Scorpio'
        WHEN DATEADD(YEAR,-DATEDIFF(YEAR,'19000101',DateOfBirth),DateOfBirth) BETWEEN CAST('19001122' AS DATETIME) AND CAST('19001221' AS DATETIME) THEN 'Sagittarius'
        WHEN DATEADD(YEAR,-DATEDIFF(YEAR,'19010101',DateOfBirth),DateOfBirth) BETWEEN CAST('19001222' AS DATETIME) AND CAST('19010119' AS DATETIME) THEN 'Capricorn'
        WHEN DATEADD(YEAR,-DATEDIFF(YEAR,'19000101',DateOfBirth),DateOfBirth) BETWEEN CAST('19000120' AS DATETIME) AND CAST('19000219' AS DATETIME) THEN 'Aquarius'
        WHEN DATEADD(YEAR,-DATEDIFF(YEAR,'19000101',DateOfBirth),DateOfBirth) BETWEEN CAST('19000220' AS DATETIME) AND CAST('19000320' AS DATETIME) THEN 'Pisces'
    END
FROM @temp

drop function if exists [dbo].convert_datetime_to_zodiac;
go
create function [dbo].convert_datetime_to_zodiac(
    @dtm       datetime2)
returns table with schemabinding as return 
with z_cte(start_mo, start_day, end_mo, end_day, zodiac_sign) as (
    select start_mo, start_day, end_mo, end_day, zodiac_sign 
    from (values (3, 21, 4, 19, 'Ares'),
                 (4, 20, 5, 19, 'Taurus'),
                 (5, 21, 6, 19, 'Gemini'),
                 (6, 21, 7, 19, 'Cancer'),
                 (7, 23, 8, 19, 'Leo'),
                 (8, 23, 9, 19, 'Virgo'),
                 (9, 23, 10, 19, 'Libra'),
                 (10, 23, 11, 19, 'Scorpio'),
                 (11, 22, 12, 19, 'Sagittarius'),
                 (12, 22, 12, 31, 'Capricorn'),
                 (1, 1, 1, 19, 'Capricorn'),
                 (1, 20, 2, 19, 'Aquarius'),
                 (2, 20, 3, 20, 'Pisces'))
                 zodiac_table(start_mo, start_day, 
                              end_mo, end_day, zodiac_sign))
select z.zodiac_sign
from z_cte z
     cross apply (select year(@dtm) dtm_yr) dtm
where cast(@dtm as date) between datefromparts(dtm.dtm_yr, z.start_mo, z.start_day)
                             and datefromparts(dtm.dtm_yr, z.end_mo, z.end_day);
go

select * from dbo.convert_datetime_to_zodiac('05/02/1980 00:00:00');
select * from dbo.convert_datetime_to_zodiac('01/22/1980 00:00:00');
select * from dbo.convert_datetime_to_zodiac('01/02/1980 00:00:00');

zodiac_sign
Taurus

zodiac_sign
Aquarius

zodiac_sign
Capricorn

with table_containing_a_date_cte as (
    select cast('03/27/1999 00:00:00' as datetime) as dtm)
select conv.zodiac_sign
from table_containing_a_date_cte dc
     cross apply dbo.convert_datetime_to_zodiac(dc.dtm) conv;

zodiac_sign
Ares