从字符串转换日期和/或时间时，消息241，级别16，状态1，第1行转换失败。 [英] Msg 241, level 16, state 1, line 1 conversion failed when converting date and/or time from character string.

问题描述

这是我的查询，当我执行查询时，我得到这个错误：
消息241，级别16，状态1，行1转换从字符串转换日期和/或时间时失败。


为了找到问题，我测试并检查了我的查询中的每个 DATEDIFF ，我什么也没发现！也许我错了，也许有些东西我无法看到它，所以，有人可以帮助并指出我正确的方向。谢谢。

This is my Query , when i executed the Query i get this erorr:
Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

And for finding the problem , i tested and checked every DATEDIFF in my Query
and i dont find anything ! maybe i'm wrong ,maybe there is something i just cant see it ,
so, Can someone please help and point me in the right direction. Thanks.

SELECT
    X, X + ' ' + X AS X,
    X AS X, 
    CASE 
    WHEN X IS NOT NULL THEN
    DATEDIFF(YEAR,CONVERT(date,STUFF(LEFT(X, 4), 3, 0, '.') + '.' + CAST(DATEPART(YEAR, GETDATE()) / 100 - 1 as char(2)) + SUBSTRING(X, 5, 2), 104) , GETDATE()) 
    ELSE
    NULL
    END As X ,
    
     (CASE WHEN right(rtrim(X), 1) IN ('1', '3', '5', '7', '9') THEN 'M'
                 WHEN right(rtrim(X), 1) IN ('2', '4', '6', '8', '0') THEN 'K'
            END) as X,
    
    Convert(varchar(10), X, 104) AS X ,
    
    (CASE WHEN X IS NOT NULL
          THEN CAST(DATEDIFF(YEAR, X, X) as VARCHAR(255))
          ELSE (CAST(DATEDIFF(year, X, GETDATE()) AS varchar(4)) +' year ' +
                CAST(DATEDIFF(month, DATEADD(year, DATEDIFF(year, X, GETDATE()), X), GETDATE()) AS varchar(2)) +' month ' +
                CAST(DATEDIFF(day, DATEADD(month, DATEDIFF(month, DATEADD(year, DATEDIFF(year, WorkStartDate, GETDATE()), WorkStartDate), GETDATE()), DATEADD(year, DATEDIFF(yy, WorkStartDate, GETDATE()), WorkStartDate)), GETDATE()) AS varchar(2)) +' day'
              )
    END) as X,
    
    X AS Afdelinger
    
    FROM Y  INNER JOIN Y ON Y=Y
    WHERE Y order by Y;

我是什么尝试过：



i测试并检查我的查询中的每个DATEDIFF

What I have tried:

i tested and checked every DATEDIFF in my Query

推荐答案

检查你的 CONVERT（日期，STUFF（... 代码及其正在处理的数据（我们无法看到，我们无法访问您的数据库） - 错误是抱怨字符串（可能是STUFF操作的结果）无法转换为日期或日期时间，因此这是最可能的罪魁祸首。

我们不能为你做到这一点 - 我们无法访问您的数据库。





对于ex，1111112222（DDMMYY-XXXX），SSN看起来像这样，我写的sql将从左边（生日）选择4个第一个数字，并用'分隔它们。 '然后拿当前时间减去生日=给我员工年龄，所有这些关于从他们的SSN.i找到员工的年龄只是单独选择，我再次得到此错误：从字符串转换日期和/或时间时转换失败。







如果格式是DDMMYYSSSS那么就不要拿前四 - 取前六位，然后使用它们。如果你看看你的STUFF生成了什么，那就是11.11，这根本不是一个日期。

如果你使用的是SQL Server 2012或更高版本，那么有一个DATEFROMPARTS函数可以提供帮助。

这是一个为你提取日期的函数：

Check your CONVERT(date, STUFF(... code and the data it is working on (which we can't see, we don;t have access to your database) - the error is complaining that a character string (the result of the STUFF operation, probably) cannot be converted to a date or datetime, so that's the most likely culprit.
We can't do that for you - we have no access to your DB.


SSN look like this for ex , 1111112222 (DDMMYY-XXXX) and the sql i wrote will select 4 first number from left (birthday) and will seprate them with '.' and then take the current time minus birthday = give me age of employees ,all this about find age of employees from their SSN.i just separate select and i get this error again : Conversion failed when converting date and/or time from character string.



If the format is DDMMYYSSSS then don't take the first four - take the first six in bits, and use them. If you look at what your STUFF generates, it's "11.11" which isn't a date at all.
If you are using SQL Server 2012 or higher, then there is a DATEFROMPARTS function which should help.
Here is a function that extracts the date for you:

CREATE FUNCTION GetDateFromSSN (@SSN NVARCHAR(10))
RETURNS DATE
AS
BEGIN
    DECLARE @Result DATE
    DECLARE @Y INT
    DECLARE @M INT
    DECLARE @D INT
    SET @Y = CONVERT(INT, SUBSTRING(@SSN, 5, 2))
    IF @Y > 17  SET @Y = @Y + 1900 ELSE SET @Y = @Y + 2000
    SET @M = CONVERT(INT, SUBSTRING(@SSN, 3, 2))
    SET @D = CONVERT(INT, SUBSTRING(@SSN, 1, 2))
    SET @RESULT = DATEFROMPARTS(@Y, @M, @D)
    RETURN @Result    
END

它使用起来很简单：

And it's simple to use:

DECLARE @SSN NVARCHAR(10)
SET @SSN = '1112132222'
SELECT dbo.GetDateFromSSN(@SSN)

这篇关于从字符串转换日期和/或时间时，消息241，级别16，状态1，第1行转换失败。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！