从字符串转换日期和/或时间时,消息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 yourCONVERT(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屋!