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())

Answer 2

This was may error. I was working with another table and forgot batchID was not the same for both. The concatenated batchID in the table I posted a question about can't be converted to a date.

Answer 3

Could you provide some more details. Namely, what does batchid look like in 8 digit form? is it YYYYMMDD or DDMMYYYY or MMDDYYYY?

Also could you show us the result of the following?

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

Sry for using an answer, i don't have the rep to add a comment directly below.