时间戳

首页 > 解决方案 > 将日期时间转换为生肖名称

sql-server - 将日期时间转换为生肖名称

问题描述

我有一个人的出生日期为“12/02/1980 00:00:00”，但我想将其转换为生肖名称。所以我希望12/02/1980显示为Sagittarius。那有可能吗？

标签： sql-serverdatabasetsqldatetime

解决方案

由于有一个固定的规则来匹配生日和生肖，最简单的方法是 CASE：

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

推荐阅读