SQL Server 2008 Date Conversion

if your data for MSH7DateTime like this one 20120413150637
and you wish this format mm/dd/yyyy

try

select SUBSTRING (cast(MSH7DateTime as varchar(50)),5,2)+'/'+SUBSTRING (cast(MSH7DateTime as varchar(50)),7,2) + '/' +left(cast(MSH7DateTime as varchar(50)),4)
from RejectedMessages

What is the datatype of column MSH7DateTime.

What is the format in which you want your output in?
What is the datatype of MSH7DateTime column? Is it varchar?
What is the exact problem you are facing?

In order for the 101 to make its conversion, you have to have a Datetime column-type.

If it is a string, then it will just put it in the varchar unchanged.

CONVERT will not convert the string you currently have, into a date, so you will have to either use the SUBSTRING method or split the value in half and make it a date convertable string.

Ok I tied this and it errored out. I am trying to pull data for yesterday.

SELECT * FROM RejectedMessages
WHERE ErrorMessage LIKE 'Error:Invalid ADM - Item Usage Update - FT1.7.1%'
AND MSH7DateTime = (SUBSTRING (cast(MSH7DateTime as varchar(50)),5,2)+'/'+SUBSTRING (cast(MSH7DateTime as varchar(50)),7,2) + '/'
+left(cast(MSH7DateTime as varchar(50)),4)) - 1
ORDER BY MSH7DateTime

Ok I tied this and it errored out. I am trying to pull data for yesterday.
Is there any chance you can answer the questions:
What is the data type for MSH7DateTime?
What error message are you getting?

Incidentally, stating that you do not know or do not wish to tell us is a perfectly viable option.  It just tells us you are not serious about getting an answer and we can go and help someone else.

Thanks