Use SQL to calculate the birthday Constellation

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 think about it, try to compare it with numbers (the comparison between the time and the date is basically the same after writing). Because the time range of the constellation is fixed, there will be no repetition, then I can convert the time into numbers. For example, if Aquarius is 1/20 to 2/18, then I can convert it to 120 to 218, but then there is a problem, such as today's date, in March 4, after being converted to a number, it will be changed to 34 (which should be 304). If there is no 0 in the middle, we cannot find the interval. This is always incorrect, the last step is to change the time to a number!

 
 

  
  
SELECT @NowDate = (CAST(MONTH(@Birthday) AS VARCHAR(10)) + CAST((CASE WHEN DAY(@Bir  
  
  
thday) < 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-01/19) Capricorn
  
  
Aquarius (01/20-02/18)
  
  
Pisces (02/19-03/20) Pisces
  
  
Aries (03/21-04/20) Aries
  
  
Taurus (04/21-05/20) Taurus
  
  
Gemini (05/21-06/21)
  
  
Cancer (06/22-07/22) Cancer
  
  
Leo (07/23-08/22) Leo
  
  
Virgo (08/23-09/22)
  
  
Libra (09/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
  
  
@ TABLE
  
  
VALUES
  
  
('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 );
  
  
SELECT
  
  
Name_zh,
  
  
Name_en,
  
  
S_time,
  
  
O_time
  
  
FROM
  
  
@ TABLE
  
  
WHERE
  
  
@ NowDate BETWEEN s_time AND o_time; <BR>
 
 

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

Edit recommendations]