Printed 2015/10/04 07:40AM

Meme Monday SQLstrology – Table Valued Zodiac Function

By Jen McCown, 2011/07/11

It’s Meme Monday time! Our topic is SQL Horoscopes, or more catchily, SQLStrology.

We’re going to have to figure out what astrological sign our SQL Server instance has – when it was “born” (installed) – before we can apply any kind of horoscope predictions.  I’ll start with the base query to find the installation date:

SELECT  createdate as Sql_Server_Install_Date
FROM    sys.syslogins
where   sid = 0x010100000000000512000000 -- language neutral
        -- loginname = 'NT AUTHORITY\SYSTEM' -- only English language installations

(Thanks to Mladen P for the SQL Server install date query.)

Now let’s turn this into a function we can use on any SQL Server instance.  There are several different ways to code this function; I chose to go with a table variable return, and a table variable to hold the Zodiac fields; I could just have easily accomplished this with a CASE statement to populate the return table, but I think readability would suffer:

CREATE FUNCTION dbo.GetServerHoroscope()
RETURNS @ServerHoroscope TABLE (
    ServerName NVARCHAR(200),
    SqlServerInstallDate DATE,
    ZodiacSign NVARCHAR(20),
    QuickCharacteristics  NVARCHAR(400))
 Author:  Jennifer McCown 

 Create date: 7/11/2011

    This work by Jennifer McCown is licensed under a Creative Commons
    Attribution-Noncommercial-No Derivative Works 3.0 United States License.

    (Which means, use it all you want, break it up, long as you
    attribute me.)

    This function uses the SQL Server installation date to determine the installation's
    zodiac sign, and also returns quick astrological characteristics.

 Example execution:
    SELECT * FROM dbo.GetServerHoroscope();

    DECLARE @SqlServerInstallDate DATE;

    DECLARE @SQLStrology TABLE (
        ZodiacSign NVARCHAR(20),
        START  NVARCHAR(20),
        [END]  NVARCHAR(20),
        StartDate DATE,
        EndDate DATE,
        QuickCharacteristics  NVARCHAR(400));


    /*  Thanks to Mladen Pradjic for this SQLServerInstallDate query. See
    SELECT @SqlServerInstallDate = createdate
    FROM    sys.syslogins
    WHERE   sid = 0x010100000000000512000000 -- language neutral
            -- loginname ='NT AUTHORITY\SYSTEM' -- only English language installations

    /* Zodiac dates and characteristics pulled from  */
    INSERT INTO     @SQLStrology (ZodiacSign,START, [END], QuickCharacteristics)
    VALUES    ('Aries', '03/21', '04/9', 'Active, Demanding, Determined, Effective, Ambitious'),
            ('Taurus', '04/20', '05/20', 'Security, Subtle strength, Appreciation, Instruction, Patience'),
            ('Gemini', '05/21', '06/20', 'Communication, Indecision, Inquisitive, Intelligent, Changeable'),
            ('Cancer', '06/21', '07/22', 'Emotion, Diplomatic, Intensity, Impulsive, Selective'),
            ('Leo', '07/23', '08/22', 'Ruling, Warmth, Generosity, Faithful, Initiative'),
            ('Virgo', '08/23', '09/22', 'Analyzing, Practical, Reflective, Observation, Thoughtful'),
            ('Libra', '09/23', '10/22', 'Balance, Justice, Truth, Beauty, Perfection'),
            ('Scorpio', '10/23', '11/21', 'Transient, Self-Willed, Purposeful, Unyielding'),
            ('Sagittarius', '11/22', '12/21', 'Philosophical, Motion, Experimentation, Optimism'),
            ('Capricorn', '12/22', '01/19', 'Determination, Dominance, Perservering, Practical, Willful'),
            ('Aquarius', '01/20', '02/18', 'Knowledge, Humanitarian, Serious, Insightful, Duplicitous'),
            ('Pisces', '02/19', '03/20', 'Fluctuation, Depth, Imagination, Reactive, Indecisive');

    -- Use the NVARCHAR start and end date strings and the SQL Server install date to build actual zodiac dates:
    UPDATE @SQLStrology SET StartDate = CAST(START + '/'+ CAST(YEAR(@SqlServerInstallDate) AS CHAR(4)) AS DATE),
        EndDate = CAST([END] + '/'+ CAST(YEAR(@SqlServerInstallDate) AS CHAR(4)) AS DATE);

    -- Insert the one applicable row into the output table:
    INSERT INTO @ServerHoroscope
    SELECT @@ServerName     as ServerName
        , @SqlServerInstallDate  AS SqlServerInstallDate
        , ZodiacSign
        , QuickCharacteristics
    FROM @SQLStrology
    WHERE @SqlServerInstallDate BETWEEN StartDate AND EndDate;

    -- RETURN --


This was a fun exercise. For more on multi-statement table-valued functions, see the SQL Server Books Online article CREATE FUNCTION, especially example C.

I look forward to updating the function with your SQL-specific SQLstrology characteristics.

Happy days,
Jen McCown

Copyright © 2002-2015 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.