将varchar转换为datetime时出错 [英] error converting varchar to datetime

问题描述

大家好，

我已经在sqlserver2008中创建了一个存储过程，如下所示.

Hi all,

I have created a stored procedure in sqlserver2008 as below.

ALTER PROCEDURE SampleP1

AS 
BEGIN 

 DECLARE @DateList varchar(1000) 
 
		 	
	 
		SELECT @DateList =  COALESCE(NotificationDate + ', ', '') + CONVERT(varchar(10), NotificationDate,20)
		 	FROM LHGroupMembers
		   WHERE GroupID IN (SELECT GroupID FROM LHGroups WHERE MatterNumber='676767676767')
		   AND NotificationDate IS NOT null
ORDER BY EmployeeNumber,GroupID
	
		  
		END 	
RETURN @@ERROR
GO

我创建了程序，当我尝试执行时

I created the procedure and when i tried to excute

DECLARE @DateList varchar(1000)
EXEC SampleP1
SELECT @DateList

我收到以下错误
varchar数据类型到datetime数据类型的转换导致超出范围的值.严重级别16状态3

请帮我解决这个问题.

预先感谢，
Sruthi R

I got the following error
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. Severity 16 State 3

Please help me solve this issue.

Thanks in advance,
Sruthi R

推荐答案

我不确定存储过程的用途.

如果将

I am not sure about the purpose of the stored procedure.

if you replace

COALESCE(NotificationDate + '', '', '''')

替换为

COALESCE(NotificationDate , '''')

，则存储过程将起作用，但不确定是否适合您的用途.

如果这不符合您的目的，那么可以请您在select语句中解释您要实现的目标.


更新2(在评论后)

the stored procedure will work but not sure whether it will suit your purpose.

If this does not suit your purpose, then could you please explain what you are trying to achieve in the select statement.


UPDATE 2 (After the comments)

ALTER PROCEDURE SampleP1

AS
BEGIN

 DECLARE @DateList varchar(1000)



 SELECT @DateList =  COALESCE(@DateList + ', ', '') + CONVERT(varchar(10), NotificationDate,20)
 FROM LHGroupMembers
 WHERE GroupID IN (SELECT GroupID FROM LHGroups WHERE MatterNumber='676767676767')
 AND NotificationDate IS NOT null
 ORDER BY EmployeeNumber,GroupID

 select @DateList

END

GO

这篇关于将varchar转换为datetime时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！