CODEWITHSUNDEEP
sqlsql-server
Crezzer7
Crezzer7

Reputation: 2335

SQL Server list of birthdays based on month and day

I currently have a table populated with names, there day of birth and the month of birth.

Example Table Data:

Name           Day       Month
Joe Bloggs     21         10
Billy Earner   12         6

I have constructed a query which will populate there birthday for the current year (2016):

SELECT
MyCalendarID,
Name,     
CAST(CAST(DATEPART(YEAR, GETDATE()) AS varchar) + '-' + CAST(TheMonth AS varchar) + '-' + CAST(TheDay AS varchar) AS DATE) AS TheBirthday
FROM MyCalendar

Example Data From Query:

Name                DOB
Joe Bloggs      2016-10-21
Billy Earner    2016-06-12

however I want to be able to populate this for the next year aswell within the same table. so this table will display there birthdays for 2016, and 2017.

Question: Is this possible, and if yes, how would I go about generating this view in SQL Server

Expected Results:

Name                DOB
Joe Bloggs      2016-10-21
Billy Earner    2016-06-12
Joe Bloggs      2017-10-21
Billy Earner    2017-06-12

Additional Information:

As I don't want this view to get to big, I only want the view to display the current year, and the next year. So as soon as 2017 comes around, I want this view to adjust to show 2017, and 2018

Thankyou for help on this matter, im hoping its not going to be to difficult

Table Creation and Data:

CREATE TABLE MyCalendar (MyCalendarID INT IDENTITY (1,1) NOT NULL,
Name VARCHAR(50),
TheDay INT,
TheMonth INT)

ALTER TABLE MyCalendar ADD CONSTRAINT MyCalendarID_PK PRIMARY KEY (MyCalendarID)

INSERT INTO MyCalendar VALUES ('Joe Bloggs', 21, 10)
INSERT INTO MyCalendar VALUES ('Billy Earner', 12, 6)

Upvotes: 3

Views: 796

Answers (3)

Bill Tür stands with Ukraine
Bill Tür stands with Ukraine

Reputation: 3515

You could use a Common Table Expression (CTE) to get the desired years and then join it with your table:

; WITH Years AS
(
    SELECT YEAR(GETDATE()) AS TheYear
    UNION SELECT YEAR(GETDATE()) + 1
)
SELECT
MyCalendarID,
Name,     
CAST(CAST(TheYear AS varchar) + '-' + CAST(TheMonth AS varchar) + '-' + CAST(TheDay AS varchar) AS DATE) AS TheBirthday
FROM MyCalendar
INNER JOIN Years ON 1 = 1;

Upvotes: 2

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

SELECT
MyCalendarID,
Name,     
CAST(CAST( YEAR(GETDATE()) AS varchar(4)) + '-' + CAST(TheMonth AS varchar) + '-' + CAST(TheDay AS varchar) AS DATE) AS TheBirthday
FROM MyCalendar

UNION ALL

SELECT
MyCalendarID,
Name,     
CAST(CAST( YEAR(GETDATE()) + 1 AS varchar(4)) + '-' + CAST(TheMonth AS varchar) + '-' + CAST(TheDay AS varchar) AS DATE) AS TheBirthday
FROM MyCalendar

Upvotes: 3

John Cappelletti
John Cappelletti

Reputation: 81990

You can use a simple CROSS APPLY and DateFromParts()

Declare @YourTable table (Name varchar(50),Day int,Month int)
Insert Into @YourTable values
('Joe Bloggs',     21,         10),
('Billy Earner',   12 ,        6)


Select A.Name
      ,B.DOB
 From  @YourTable A
 Cross Apply ( Values (DateFromParts(Year(GetDate())  ,A.Month,A.Day))
                     ,(DateFromParts(Year(GetDate())+1,A.Month,A.Day))
             ) B (DOB)

Returns

Name            DOB
Joe Bloggs      2016-10-21
Joe Bloggs      2017-10-21
Billy Earner    2016-06-12
Billy Earner    2017-06-12

Upvotes: 6

Related Questions