SQL-calculate the constellation of the birthday

Just after lunch, I took a break. I saw a problem in the QQ group. I told you a date without a year and figured out the constellation of the date; the first idea at the beginning was to compare the start time and start time of the constellation to the start time. Then we can see the constellation in the distance! But then I thought, is there any other method besides the comparison of time intervals?
If you want to do it, try to compare it with a number (it is basically the same as the date );
Because the time interval of the constellation is fixed and there are no duplicates, I can convert the time to a number. For example, if the Aquarius is 1/20 to 2/18, then I can convert it to 120 to 218, but then there is a problem. For example, today's date, January 1, March 4 will change to 34 (which should be 304) after it is converted to a number, with 0 missing in the middle, why can't we find the interval? The calculation is always incorrect. In the end, we can only change the time to a number!

 

 
Select @ nowdate = (cast (month (@ birthday) as varchar (10) + Cast (case when day (@ birthday) <10 then '0' + Cast (Day (@ birthday) as varchar (10) else cast (Day (@ birthday) as varchar (10) end) as varchar (10 )));
 

The most troublesome way to calculate the constellation is to convert the date in the early stage. The time has been changed, and the rest is easy to do. Let's make a comparison of the number intervals, you can get the constellation you want!

 

/* Constellation date (Gregorian calendar) English name Capricorn (12/22-1/19) Capricorn Aquarius (1/20-2/18) Aquarius Pisces (2/19-3/20) Pisces aries (3/21-4/20) aries taurus (4/21-5/20) Taurus Gemini (5/21-6/21) gemini cancer (6/22-7/22) cancer leo (7/23-8/22) Leo Virgo (8/23-9/22) virgo Libra (9/23-10/22) Libra SCORPIO (10/23-11/21) scorpio sagittarius (11/22-12/21) Sagittarius */declare @ Table (name_zh varchar (128 ), name_en varchar (128), s_time int, o_time INT); declare @ nowdate int; declare @ birthday datetime; Set @ Birthday = getdate () select @ nowdate = (cast (month (@ birthday) as varchar (10) + Cast (case when day (@ birthday) <10 then '0' + Cast (Day (@ birthday) as varchar (10) else cast (Day (@ birthday) as varchar (10) end) as varchar (10); insert into @ tablevalues ('capricorn ', 'capricorn', 1222,119), ('aquarius', 'aquarius', 120,218 ), ('pisces ', 'pisces', 219,320), ('aries, 'aries', 321,420), ('taurus, 'taurus', 421,520 ), ('Gemini', 'gemini ', 521,621), ('cancer', 'cancer', 622,722), ('Leo ', 'Leo', 723,822 ), ('virgo', 'virgo', 823,922), ('libra', 'libra', 923,102 2), ('scorpio ', 'scorpio', 1023,112 1 ), ('sagittarius ', 'sagittarius', 1122,122 1); If @ nowdate> 1222 beginselect 'capricorn 'name_zh, 'capricorn' name_en, '2016' s_time, '2016' o_timeendelsebeginselect name_zh, name_en, s_time, o_timefrom @ table where @ nowdate between s_time and o_time; End

This method has not been carefully verified, and may cause bugs or performance losses. It will be rewritten in the future!

Eg:

In order to deal with the first and last problems in Capricorn, it seems that this can only be solved!