converting datetime error message

[Mary10k]

Hello,
I am getting the following error message when tryin to run the code listed below:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Code:
SELECT T1.EMPI, T1.DATE, T1.VALUE
FROM plmCombinedLab AS T1
WHERE T1.EMPI IN
(SELECT TOP 2 T2.Date
FROM plmCombinedLab As T2
WHERE T1.EMPI = T2.EMPI
ORDER BY T2.DATE DESC)
My date field is a datetime type in the SQL database. Could you please advise how and if I need to do some type of conversion?
Thank you.

 

[mercwrought]

looks like your trying to compair t1.empi to a date time and it can not conver it. your

(SELECT TOP 2 T2.Date
    FROM plmCombinedLab As T2
        WHERE T1.EMPI = T2.EMPI
        ORDER BY T2.DATE DESC)

need to return 2 employies not 2 dates

 

[Mary10k]

I'm not clear on your response. I ran your example and received the same error. Could you please elaborate?
Thank you.

 

[mercwrought]

mine was not an example. I was trying to show you where your code goes wrong. the

(SELECT TOP 2 T2.Date
    FROM plmCombinedLab As T2
        WHERE T1.EMPI = T2.EMPI
        ORDER BY T2.DATE DESC)

is trying to return 2 dates. i said trying too it can not because of the

WHERE T1.EMPI = T2.EMPI

it looks like your trying to get the top 2.... you know im not real sure what you are trying to do. if this code does not help please explain what you are trying to do

SELECT top 2 EMPI, DATE, VALUE
FROM plmCombinedLab
ORDER BY DATE DESC

 

[Mary10k]

I am trying to return the top 2 dates and values for each empi. If I ran Select T1.empi, t1.date, t1.value
FROM plmCombinedLab as T1
Where T1.Date IN (SELECT TOP 2 T2.DATE FROM plmcombinedlab As T2 Where T1.EMPI = T2. EMPI ORDER BY T2.EMPI)

would this return the correct top 2 lab values for each empi listed in the table?
Thanks.

 

[mercwrought]

i see what you are trying to do now. i keep seeing this question but i have never read any of the answers and i can not find any right now. i will think on it and see what i can come up with.

 

[mercwrought]

ok i think i got it

SELECT T1.EMPI, T1.DATE, T1.VALUE
FROM plmCombinedLab AS T1
WHERE T1.date IN
(SELECT TOP 2 T2.Date
    FROM plmCombinedLab As T2
        WHERE T1.EMPI = T2.EMPI
        ORDER BY T2.DATE DESC)

you were still compairing an employ to a date and that is where the problem was