Today we’ll learn how you can use T-SQL to calculate your zodiac sign from your birthdate.
“Heeeeeere’s Johnny!” – Ed McMahon introducing Johnny Carson for the Tonight Show
If you’re old enough to remember the Tonight Show hosted by Johnny Carson, how can you not remember the iconic Stupid Pet Tricks skits, and the later tributes to those done by David Letterman? My Stupid T-SQL Tricks are hopefully a bit more useful, but just as amusing as Carson’s Stupid Pet Tricks were!
Everyone knows the twelve signs of the zodiac, and there’s a pretty good chance you also know what your sign is, and that which sign of the zodiac you are depends on your date of birth.
Putting the Zodiac Signs into a Lookup Table
Four our lookup table, we’ll use a Common Table Expression (CTE) with the twelve zodiac signs, but with one repeated.
WITH Zodiac
(
SignNo, SignName, Symbol, StartDT, EndDT, SignIs
)
AS
(
SELECT 1, 'Aries', NCHAR(9800), '1900-03-21', '1900-04-20', 'Ram'
UNION ALL SELECT 2, 'Taurus', NCHAR(9801), '1900-04-21', '1900-05-21', 'Bull'
UNION ALL SELECT 3, 'Gemini', NCHAR(9802), '1900-05-22', '1900-06-21', 'Twins'
UNION ALL SELECT 4, 'Cancer', NCHAR(9803), '1900-06-22', '1900-07-22', 'Crab'
UNION ALL SELECT 5, 'Leo', NCHAR(9804), '1900-07-23', '1900-08-22', 'Lion'
UNION ALL SELECT 6, 'Virgo', NCHAR(9805), '1900-08-23', '1900-09-23', 'Maiden'
UNION ALL SELECT 7, 'Libra', NCHAR(9806), '1900-09-24', '1900-10-23', 'Scales'
UNION ALL SELECT 8, 'Scorpio', NCHAR(9807), '1900-10-24', '1900-11-22', 'Scorpion'
UNION ALL SELECT 9, 'Sagitarius', NCHAR(9808), '1900-11-23', '1900-12-21', 'Archer (Centaur)'
UNION ALL SELECT 10, 'Capricorn', NCHAR(9809), '1900-12-22', '1900-12-31', 'Sea-Goat (Goat)'
UNION ALL SELECT 10, 'Capricorn', NCHAR(9809), '1900-01-01', '1900-01-20', 'Sea-Goat (Goat)'
UNION ALL SELECT 11, 'Aquarius', NCHAR(9810), '1900-01-21', '1900-02-19', 'Water-bearer'
UNION ALL SELECT 12, 'Pisces', NCHAR(9811), '1900-02-20', '1900-03-20', 'Fish'
)
SELECT *
FROM Zodiac;
The signs are numbered in the order that they’re typically described in the literature, e.g., on the Wiki page for The Zodiac. Note how Capricorn is duplicated, because the dates for that sign cross the end-of-year boundary. Here are the displayed results from our table:
SignNo SignName Symbol StartDT EndDT SignIs 1 Aries ♈ 1900-03-21 1900-04-20 Ram 2 Taurus ♉ 1900-04-21 1900-05-21 Bull 3 Gemini ♊ 1900-05-22 1900-06-21 Twins 4 Cancer ♋ 1900-06-22 1900-07-22 Crab 5 Leo ♌ 1900-07-23 1900-08-22 Lion 6 Virgo ♍ 1900-08-23 1900-09-23 Maiden 7 Libra ♎ 1900-09-24 1900-10-23 Scales 8 Scorpio ♏ 1900-10-24 1900-11-22 Scorpion 9 Sagitarius ♐ 1900-11-23 1900-12-21 Archer (Centaur) 10 Capricorn ♑ 1900-12-22 1900-12-31 Sea-Goat (Goat) 10 Capricorn ♑ 1900-01-01 1900-01-20 Sea-Goat (Goat) 11 Aquarius ♒ 1900-01-21 1900-02-19 Water-bearer 12 Pisces ♓ 1900-02-20 1900-03-20 Fish
I think it is kind of cool that we could use the NCHAR built-in T-SQL function (introduced in SQL 2005) to return the UNICODE symbol for each zodiac sign!
Suppose we’d now like to calculate the sign for a birthdate. We’ll use two cases, a Capricorn and a Gemini to illustrate how easy it is using the lookup table above.
DECLARE @BirthDate DATE = '1999-01-12';
WITH Zodiac
(
SignNo, SignName, Symbol, StartDT, EndDT, SignIs
)
AS
(
SELECT 1, 'Aries', NCHAR(9800), '1900-03-21', '1900-04-20', 'Ram'
UNION ALL SELECT 2, 'Taurus', NCHAR(9801), '1900-04-21', '1900-05-21', 'Bull'
UNION ALL SELECT 3, 'Gemini', NCHAR(9802), '1900-05-22', '1900-06-21', 'Twins'
UNION ALL SELECT 4, 'Cancer', NCHAR(9803), '1900-06-22', '1900-07-22', 'Crab'
UNION ALL SELECT 5, 'Leo', NCHAR(9804), '1900-07-23', '1900-08-22', 'Lion'
UNION ALL SELECT 6, 'Virgo', NCHAR(9805), '1900-08-23', '1900-09-23', 'Maiden'
UNION ALL SELECT 7, 'Libra', NCHAR(9806), '1900-09-24', '1900-10-23', 'Scales'
UNION ALL SELECT 8, 'Scorpio', NCHAR(9807), '1900-10-24', '1900-11-22', 'Scorpion'
UNION ALL SELECT 9, 'Sagitarius', NCHAR(9808), '1900-11-23', '1900-12-21', 'Archer (Centaur)'
UNION ALL SELECT 10, 'Capricorn', NCHAR(9809), '1900-12-22', '1900-12-31', 'Sea-Goat (Goat)'
UNION ALL SELECT 10, 'Capricorn', NCHAR(9809), '1900-01-01', '1900-01-20', 'Sea-Goat (Goat)'
UNION ALL SELECT 11, 'Aquarius', NCHAR(9810), '1900-01-21', '1900-02-19', 'Water-bearer'
UNION ALL SELECT 12, 'Pisces', NCHAR(9811), '1900-02-20', '1900-03-20', 'Fish'
)
SELECT BirthDate=@BirthDate, a.SignNo, a.SignName, SignIs='The ' + a.SignIs, a.Symbol
FROM Zodiac a
WHERE @BirthDate BETWEEN
DATEADD(year, DATEDIFF(year, a.StartDT, @BirthDate), a.StartDT) AND
DATEADD(year, DATEDIFF(year, a.EndDT, @BirthDate), a.EndDT);
Which returns one row for our Capricorn:
BirthDate SignNo SignName SignIs Symbol 1999-01-12 10 Capricorn The Sea-Goat (Goat) ♑
You can see how we’ve used the DATEADD and DATEDIFF functions to place our birthdate between the StartDT and EndDT columns within our lookup table.
A Function to Calculate the Sign of the Zodiac for a Birth Date
I like in-line, Table Valued Functions (iTVFs) because I consider them tools. This little T-SQL query is easy enough to put into an iTVF, so let’s do that now.
CREATE FUNCTION dbo.ZodiacSign
(
@BirthDate DATE
) RETURNS TABLE WITH SCHEMABINDING
RETURN
WITH Zodiac
(
SignNo, SignName, Symbol, StartDT, EndDT, SignIs
)
AS
(
SELECT 1, 'Aries', NCHAR(9800), '1900-03-21', '1900-04-20', 'Ram'
UNION ALL SELECT 2, 'Taurus', NCHAR(9801), '1900-04-21', '1900-05-21', 'Bull'
UNION ALL SELECT 3, 'Gemini', NCHAR(9802), '1900-05-22', '1900-06-21', 'Twins'
UNION ALL SELECT 4, 'Cancer', NCHAR(9803), '1900-06-22', '1900-07-22', 'Crab'
UNION ALL SELECT 5, 'Leo', NCHAR(9804), '1900-07-23', '1900-08-22', 'Lion'
UNION ALL SELECT 6, 'Virgo', NCHAR(9805), '1900-08-23', '1900-09-23', 'Maiden'
UNION ALL SELECT 7, 'Libra', NCHAR(9806), '1900-09-24', '1900-10-23', 'Scales'
UNION ALL SELECT 8, 'Scorpio', NCHAR(9807), '1900-10-24', '1900-11-22', 'Scorpion'
UNION ALL SELECT 9, 'Sagitarius', NCHAR(9808), '1900-11-23', '1900-12-21', 'Archer (Centaur)'
UNION ALL SELECT 10, 'Capricorn', NCHAR(9809), '1900-12-22', '1900-12-31', 'Sea-Goat (Goat)'
UNION ALL SELECT 10, 'Capricorn', NCHAR(9809), '1900-01-01', '1900-01-20', 'Sea-Goat (Goat)'
UNION ALL SELECT 11, 'Aquarius', NCHAR(9810), '1900-01-21', '1900-02-19', 'Water-bearer'
UNION ALL SELECT 12, 'Pisces', NCHAR(9811), '1900-02-20', '1900-03-20', 'Fish'
)
SELECT BirthDate=@BirthDate, a.SignNo, a.SignName, SignIs='The ' + a.SignIs, a.Symbol
FROM Zodiac a
WHERE @BirthDate BETWEEN
DATEADD(year, DATEDIFF(year, a.StartDT, @BirthDate), a.StartDT) AND
DATEADD(year, DATEDIFF(year, a.EndDT, @BirthDate), a.EndDT);
With this function, we can now calculate the Zodiac signs for a whole range of birthdates as follows:
WITH BirthDates (BirthDate) AS
(
SELECT '1998-06-17' -- A Gemini
UNION ALL SELECT '1999-01-10' -- A Capricorn
)
SELECT a.BirthDate, SignNo, SignName, SignIs, Symbol
FROM BirthDates a
CROSS APPLY dbo.ZodiacSign(a.BirthDate) b;
Which gives us these results:
BirthDate SignNo SignName SignIs Symbol 1998-06-17 3 Gemini The Twins ♊ 1999-01-10 10 Capricorn The Sea-Goat (Goat) ♑
The Zodiac Date Range in which a Birth Date Falls
Because of Capricorn, which splits two different years, were we to want to calculate the date range for the sign on which a particular birth date falls, things get just a little more complicated. For example, for our two birthdates shown above, we’d like to see a results set that looks like this.
BirthDate SignNo SignName SignIs SignPeriodStart SignPeriodEnd Symbol 1998-06-17 3 Gemini The Twins 1998-05-22 1998-06-21 ♊ 1999-01-10 10 Capricorn The Sea-Goat (Goat) 1998-12-22 1999-01-20 ♑
Notice how Capricorn’s SignPeriodStart is the year before that particular birthdate.
Our ZodiacSign function can be modified as follows to handle this case.
ALTER FUNCTION dbo.ZodiacSign
(
@BirthDate DATE
) RETURNS TABLE WITH SCHEMABINDING
RETURN
WITH Zodiac
(
SignNo, SignName, Symbol, StartDT, EndDT, SignIs
)
AS
(
SELECT 1, 'Aries', NCHAR(9800), '1900-03-21', '1900-04-20', 'Ram'
UNION ALL SELECT 2, 'Taurus', NCHAR(9801), '1900-04-21', '1900-05-21', 'Bull'
UNION ALL SELECT 3, 'Gemini', NCHAR(9802), '1900-05-22', '1900-06-21', 'Twins'
UNION ALL SELECT 4, 'Cancer', NCHAR(9803), '1900-06-22', '1900-07-22', 'Crab'
UNION ALL SELECT 5, 'Leo', NCHAR(9804), '1900-07-23', '1900-08-22', 'Lion'
UNION ALL SELECT 6, 'Virgo', NCHAR(9805), '1900-08-23', '1900-09-23', 'Maiden'
UNION ALL SELECT 7, 'Libra', NCHAR(9806), '1900-09-24', '1900-10-23', 'Scales'
UNION ALL SELECT 8, 'Scorpio', NCHAR(9807), '1900-10-24', '1900-11-22', 'Scorpion'
UNION ALL SELECT 9, 'Sagitarius', NCHAR(9808), '1900-11-23', '1900-12-21', 'Archer (Centaur)'
UNION ALL SELECT 10, 'Capricorn', NCHAR(9809), '1900-12-22', '1900-12-31', 'Sea-Goat (Goat)'
UNION ALL SELECT 10, 'Capricorn', NCHAR(9809), '1900-01-01', '1900-01-20', 'Sea-Goat (Goat)'
UNION ALL SELECT 11, 'Aquarius', NCHAR(9810), '1900-01-21', '1900-02-19', 'Water-bearer'
UNION ALL SELECT 12, 'Pisces', NCHAR(9811), '1900-02-20', '1900-03-20', 'Fish'
),
GroupedSigns AS
(
SELECT SignNo
,StartDT = CASE WHEN SignNo = 10 AND MONTH(@BirthDate) = 1
THEN DATEADD(year, -1, MAX(StartDT))
WHEN SignNo = 10 AND MONTH(@BirthDate) = 12
THEN MAX(StartDT)
ELSE MIN(StartDT)
END
,EndDT = CASE WHEN SignNo = 10 AND MONTH(@BirthDate) = 1
THEN MIN(EndDT)
WHEN SignNo = 10 AND MONTH(@BirthDate) = 12
THEN DATEADD(year, 1, MIN(EndDT))
ELSE MAX(EndDT)
END FROM Zodiac
GROUP BY SignNo
)
SELECT a.SignNo, a.SignName, SignIs='The ' + a.SignIs, a.Symbol
,SignPeriodStart = CAST(DATEADD(year, YEAR(@BirthDate)-1900, b.StartDT) AS DATE)
,SignPeriodEnd = CAST(DATEADD(year, YEAR(@BirthDate)-1900, b.EndDT) AS DATE)
FROM Zodiac a
JOIN GroupedSigns b ON a.SignNo = b.SignNo
WHERE @BirthDate BETWEEN
DATEADD(year, DATEDIFF(year, a.StartDT, @BirthDate), a.StartDT) AND
DATEADD(year, DATEDIFF(year, a.EndDT, @BirthDate), a.EndDT);
Because of our split row for Capricorn, we needed to create a grouping that set the dates accordingly and then JOIN this back to our original Zodiac lookup table. A little more date arithmetic magic, this time using the DATEADD and YEAR built-in functions, will get us to our period dates.
This can be demonstrated by running the following query to call our newly ALTERed function:
WITH BirthDates (BirthDate) AS
(
SELECT '1998-06-17' -- A Gemini
UNION ALL SELECT '1999-01-10' -- A Capricorn
)
SELECT a.BirthDate, SignNo, SignName, SignIs
,SignPeriodStart
,SignPeriodEnd
,Symbol
FROM BirthDates a
CROSS APPLY dbo.ZodiacSign(a.BirthDate) b;
Which gives us the results we seek!
BirthDate SignNo SignName SignIs SignPeriodStart SignPeriodEnd Symbol 1998-06-17 3 Gemini The Twins 1998-05-22 1998-06-21 ♊ 1999-01-10 10 Capricorn The Sea-Goat (Goat) 1998-12-22 1999-01-20 ♑
Conclusions
I love to create iTVFs as tools I can always use when some calculation gets hairy! I have many tools that are specific to their purpose. I also like to adopt iTVFs of this sort from other sources (a great example of that being DelimitedSplit8K from SQL MVP Jeff Moden). You should probably consider doing this yourself to improve your skill at both writing iTVFs and using the tools you create to accelerate your own T-SQL coding jobs.
Now if I could just figure out the Yin and the Yang of the Chinese calendar to confirm I’m a Dog that would really be something to chew on.
Yet another stupid, T-SQL trick, right? Who knows, perhaps one day you’ll even have a use for it!
Follow me on Twitter: @DwainCSQL
© Copyright Dwain Camps 16 Apr 2015. All rights reserved.
