Using DateDiff() to find the difference between getDate() and a concatonated value

Question

I am trying to find the difference between today's date and a value that is a concatenation of mulitple values but begins with an 8 digit date without any dashes or forward slashes. There's something wrong with my syntax I believe, but I'm not yet skilled enough to see what I'm doing incorrectly. Here is what I have so far:

select DateDiff(dd, (select MIN(CAST(Left(batchid, 8) as Date)) from
[Table]), getdate()) from [Table]

This is returning the following error: "Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string."

Answer 1

I think your have data where the left 8 is not a valid date in yyyymmdd format. Your can run the following query to find them

select batchid, isdate(Left(batchid, 8))
from [Table]
where isdate(Left(date, 8)) = 0

This is the correct syntax to your query. Your original example had an extra parenthesis which I assume was a typo since your error appears to be data related.

select 
    datediff(dd, (select min(cast(left(batchid, 8) as date)) 
                  from [Table]), getdate())