使用DateDiff()查找getDate()和concatonated值之间的差异

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:

我正在尝试找出今天的日期和一个值之间的区别，这个值是一个连接多个值的值，但是以一个没有破折号或斜线的8位日期开始。我认为我的语法有问题，但我还没有足够的技能来发现我做错了什么。这是我到目前为止所得到的:

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."

这将返回以下错误:“Msg 241，级别16，状态1，第1行转换在从字符串转换日期和/或时间时失败。”

3 个解决方案

#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

我认为你的数据中左边的8不是yyyymmdd格式的有效日期。您可以运行以下查询来查找它们

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

#2

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

你能提供更多的细节吗?也就是说，八位数形式的贝契德是什么样的?是YYYYMMDD还是ddmmyyy还是mddyyyyy ?

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.

Sry使用一个答案，我没有代表在下面直接添加评论。

#3

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.

这是可能的错误。我当时正在和另一张桌子一起工作，忘记了巴奇德对这两个人来说都不一样。我发布的关于不能转换为日期的问题表中连接的batchID。

#1