Calculate Date Difference in SQL Server 2019

Question

I am trying to calculate the date difference and keep getting a column name error. I am new to SQL and learning from books and YouTube. Any assistance would be appreciated. I commented out the code not working

declare @rpDT datetime
set @rpDT = getdate()
SELECT [OrgKey]
  ,[visID]
  ,[visPatID]
  ,[visInternal]
  ,[visName]
  ,[visAssignedNS]
  ,[visAssignedRoom]
  ,[visAssignedBed]
  ,[visAdmitDT]
  ,isnull([visDischargeDT],@rpDT)as disDT
  ,datediff(day,[visAdmitDT],disDT) as Pt_days
 
FROM [MH_Pharmacy_Hub].[MC].[dbo_Visits]

You can't use a column calculated in a `SELECT` in that very same `SELECT`. Repeat the expression or use a subquery or CTE. — Jeroen Mostert, Aug 15 '22 at 15:32

score 0 · Answer 1 · answered Aug 15 '22 at 15:34

SQL Server and most of the RDBMS do not allow to reference column at the same level. The closest equivalent to achieve similar effect is CROSS APPLY:

declare @rpDT datetime = getdate();

SELECT [OrgKey]
  ,[visID]
  ,[visPatID]
  ,[visInternal]
  ,[visName]
  ,[visAssignedNS]
  ,[visAssignedRoom]
  ,[visAssignedBed]
  ,[visAdmitDT]
  ,s.disDT
  ,datediff(day,[visAdmitDT],s.disDT) as Pt_days
FROM [MH_Pharmacy_Hub].[MC].[dbo_Visits]
CROSS APPLY (SELECT COALESCE([visDischargeDT],@rpDT)) AS s(disDT);

Calculate Date Difference in SQL Server 2019

1 Answers1