sql server 2012 - How to Join two tables by extracting date and hour separately? -

select      s.rollnumber, s.[payment date], s.amount, h.hourkey, d.year       [accounts].[dbo].studentfeesdetails s inner join       [abcu_dwh].[dbo].dimhouroftheday h on h.hourkey = datepart(hour, s.[payment date]) inner join      [abcu_dwh].[dbo].dimdates d on d.[full date] = s.[payment date]        rollnumber = 5 

s.[payment date] column of datetime datatype. query not produce results. commenting 1 join statement query produces results. problem in code?

if s.[payment date] datetime value has time component too, , reason you're not getting data might time part doesn't match value of [full date] (which assume date).

try casting date:

inner join [abcu_dwh].[dbo].dimdates d on d.[full date] = cast(s.[payment date] date) 

if [full date] datetime might have cast too, end comparing date part so:

on cast(d.[full date] date) = cast(s.[payment date] date)