SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Zodiac ( [Name] NCHAR(60) NOT NULL, StartDate INT NULL, EndDate INT NULL ); INSERT INTO Zodiac ([Name], StartDate, EndDate) VALUES ( 'Aries', DATEPART(dy, '2000-03-21'), DATEPART(dy, '2000-04-19') ), ( 'Taurus', DATEPART(dy, '2000-04-20'), DATEPART(dy, '2000-05-20') ), ( 'Gemini', DATEPART(dy, '2000-05-21'), DATEPART(dy, '2000-06-21') ), ( 'Cancer', DATEPART(dy, '2000-06-22'), DATEPART(dy, '2000-07-22') ), ( 'Leo', DATEPART(dy, '2000-07-23'), DATEPART(dy, '2000-08-22') ), ( 'Virgo', DATEPART(dy, '2000-08-23'), DATEPART(dy, '2000-09-22') ), ( 'Libra', DATEPART(dy, '2000-09-23'), DATEPART(dy, '2000-10-23') ), ( 'Scorpio', DATEPART(dy, '2000-10-24'), DATEPART(dy, '2000-11-21') ), ( 'Sagittarius', DATEPART(dy, '2000-11-22'), DATEPART(dy, '2000-12-21') ), ( 'Capricorn', DATEPART(dy, '2000-12-22'), DATEPART(dy, '2000-01-19') ), ( 'Aquarius', DATEPART(dy, '2000-01-20'), DATEPART(dy, '2000-02-18') ), ( 'Pisces', DATEPART(dy, '2000-02-19'), DATEPART(dy, '2000-03-20') ); CREATE TABLE Actor ( Name VARCHAR(64), BirthDate DATE ); INSERT INTO Actor VALUES ('Leonardo DiCaprio', '1974-11-11'); DECLARE @curdate DATETIME SET @curdate = GetDate() SELECT a.[Name], a.[BirthDate], datediff(year, [BirthDate], @curdate) AS [Age], z.[Name] AS [Zodiac] FROM Actor AS a JOIN Zodiac AS z ON DATEPART(dy, [BirthDate]) BETWEEN z.StartDate AND z.EndDate ORDER BY a.[BirthDate] DROP TABLE Zodiac