Extract information from date

Transact-SQL
1

Hi, I have got a table with a date and I would like to somehow extract information from that date. Not sure what to do, write a function or perhaps use an existing function.

Currently the select shown below gives this result

Id Date
5 2018-01-03 00:00:00.000
2 2018-01-17 00:00:00.000
3 2018-01-17 00:00:00.000
7 2018-01-23 00:00:00.000
8 2018-01-24 00:00:00.000
9 2018-01-26 00:00:00.000
10 2018-01-31 00:00:00.000
11 2018-02-02 00:00:00.000
4 2018-02-23 00:00:00.000
6 2018-03-23 00:00:00.000

What I would like is to have this

Id Date Extra information
5 2018-01-03 00:00:00.000 Passed date
2 2018-01-17 00:00:00.000 Passed date
3 2018-01-17 00:00:00.000 Passed date
7 2018-01-23 00:00:00.000 Today
8 2018-01-24 00:00:00.000 Tomorrow
9 2018-01-26 00:00:00.000 This week
10 2018-01-31 00:00:00.000 Next week
11 2018-02-02 00:00:00.000 Next week
4 2018-02-23 00:00:00.000 Next month
6 2018-03-23 00:00:00.000 Later

And if a date comes up as a later date the same month later than "Next week" to say "This month"

Drop table #tblTemp
GO
CREATE TABLE #tblTemp(
[t_Id] [int] IDENTITY(1,1) NOT NULL,
[t_Date] [datetime] NOT NULL
)
GO
SET IDENTITY_INSERT #tblTemp ON 
GO
INSERT #tblTemp (t_Id, t_Date) VALUES (2,  CAST(N'2018-01-17T00:00:00.000' AS DateTime))
GO
INSERT #tblTemp (t_Id, t_Date) VALUES (3,  CAST(N'2018-01-17T00:00:00.000' AS      DateTime))
GO
INSERT #tblTemp (t_Id, t_Date) VALUES (4,  CAST(N'2018-02-23T00:00:00.000' AS DateTime))
GO
INSERT #tblTemp (t_Id, t_Date) VALUES (5,  CAST(N'2018-01-03T00:00:00.000' AS DateTime))
GO
INSERT #tblTemp (t_Id, t_Date) VALUES (6,  CAST(N'2018-03-23T00:00:00.000' AS DateTime))
GO
INSERT #tblTemp (t_Id, t_Date) VALUES (7,  CAST(N'2018-01-23T00:00:00.000' AS DateTime))
GO
INSERT #tblTemp (t_Id, t_Date) VALUES (8,  CAST(N'2018-01-24T00:00:00.000' AS DateTime))
GO
INSERT #tblTemp (t_Id, t_Date) VALUES (9,  CAST(N'2018-01-26T00:00:00.000' AS DateTime))
GO
INSERT #tblTemp (t_Id, t_Date) VALUES (10,  CAST(N'2018-01-31T00:00:00.000' AS DateTime))
GO
INSERT #tblTemp (t_Id, t_Date) VALUES (11,  CAST(N'2018-02-02T00:00:00.000' AS DateTime))
GO
SET IDENTITY_INSERT #tblTemp OFF
GO
Select * from #tblTemp order by t_Date
2 
SELECT t.*,
    CASE WHEN ca1.days_diff < 0 THEN 'Prior date'
         WHEN ca1.days_diff = 0 THEN 'Today'
         WHEN ca1.days_diff = 1 THEN 'Tomorrow'
         WHEN ca1.days_diff BETWEEN 2 AND 6 THEN 'This week'
         --WHEN ...add_other_checks_here...
    END AS [Extra Information]
FROM #tblTemp t
CROSS APPLY (
    SELECT DATEDIFF(DAY, GETDATE(), t.t_Date) AS days_diff
) AS ca1