CONCAT two row values

[hexOffender]

I have a table that has a TIME value in one row, and a Date value in another row. I have a select statement that can isolate the values of each, but I want them to be together in one field. Also They are string , but I need to run DATEDIFF later. What is the best way to CONCAT the results of the two queries into one field.

 

[hexOffender]

Here is the query with the Date and Time values. In the previous post I had said two queries, but it can go either way.

SELECT distinct NQR.QueryID, NQR.Response
FROM NurQueryResults AS NQR INNER JOIN
AbstractData AS AD ON NQR.SourceID = AD.SourceID AND NQR.VisitID = AD.VisitID
WHERE NQR.QueryID IN('NUR.BDAY','NUR.TIME') and(AD.UnitNumber = @UnitNum)

Returns This:
QueryID Response
------- --------
NUR.BDAY 20100521
NUR.TIME 1542

So I need two response values in One Field that I can DATEDIFF.

 

[bborissov]

SELECT CAST(BDay.Response+' '+STUFF(BTime.Response,3,1,':') as datetime)
FROM (SELECT Response  --- Pul ALL neccesarry fields here for correct JOIN clause
      FROM NurQueryResults 
      INNER JOIN AbstractData AS AD 
           ON NQR.SourceID = AD.SourceID AND
              NQR.VisitID  = AD.VisitID  AND
              AD.UnitNumber = @UnitNum
      WHERE NQR.QueryID  = 'NUR.BDAY') BDay

INNER JOIN (SELECT Response --- Pul ALL neccesarry fields here for correct JOIN clause
      FROM NurQueryResults 
      INNER JOIN AbstractData AS AD 
           ON NQR.SourceID = AD.SourceID AND
              NQR.VisitID  = AD.VisitID  AND
              AD.UnitNumber = @UnitNum
      WHERE NQR.QueryID  = 'NUR.TIME') BTIME

 ON BTIME.???????????? = BDay.??????????????

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.