SQL Invalid Column Name When GetDate and Distinct-CodePudding

I wanted to run a query that will display the current date (I used distinct since there are multiple results) and get the jobname, endtime columns. However, I'm getting below error due to invalid column name. Hope you can help on this.

Okay, I saw this in some thread: "column_alias can be used in an ORDER BY clause, but it cannot be used in a WHERE, GROUP BY, or HAVING clause."

is there another way to use this column on where clause?

select distinct(convert(varchar, getdate(), 112)) AS TodayDate
FROM [LIB_KHP_RTP].[sap].[TBTCO] t 
inner join (select [JOBNAME], [ENDTIME], [STRTDATE] from [LIB_KHP_RTP].[sap].[TBTCO]) tm
on t.[JOBNAME] = tm.[JOBNAME] and t.TodayDate = tm.[STRTDATE]

Error:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'TodayDate'.

CodePudding user response：

It looks like your distinct doesn't need to be right up with the parentheses. I pulled the distinct away from your convert function, I would think since this is just a single column in the select adding the distinct this way should work.

select distinct convert(varchar, getdate(), 112) AS TodayDate 
FROM [LIB_KHP_RTP].[sap].[TBTCO] t 
inner join (select [JOBNAME], [ENDTIME], [STRTDATE] 
from [LIB_KHP_RTP].[sap].[TBTCO]) 
tm on t.[JOBNAME] = tm.[JOBNAME] 
and t.TodayDate = tm.[STRTDATE]

CodePudding user response：

I was now able to make it work by just using this query

select [JOBNAME], [ENDTIME],[STRTDATE] FROM [LIB_KHP_RTP].[sap].[TBTCO] where [JOBNAME] in ('Sample_Name') and (select distinct convert(varchar, getdate(), 112)) = [STRTDATE]