error converting varchar to datetime

Question

5.00/5 (1 vote)

See more:

Hi all,

I have created a stored procedure in sqlserver2008 as below.

SQL

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

SQL

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

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

Posted 4-Apr-12 22:53pm

SruthiR

Updated 4-Apr-12 23:02pm

uspatel

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bala Selvanayagam · Answer 1 · 2012-04-04T23:15:00

Solution 1

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

if you replace

COALESCE(NotificationDate + ', ', '')

with

COALESCE(NotificationDate , '')

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)

SQL

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

Posted 4-Apr-12 23:15pm

Bala Selvanayagam

Updated 5-Apr-12 1:05am

v4

Comments

SruthiR 5-Apr-12 5:45am

I tried your solution but i am getting the same error.
As an output i want to get the comma separated dates.There is a table with fields Emplyee and date as below.

Employee NotificationDate
E1 1/1/2012
E1 2/1/2012

I need to show the given dates separated by comma for a specific employee. The output should be as follows.

Employee NotificationDate
E1 1/1/2012,2/1/2012

This is the requirement.

Bala Selvanayagam 5-Apr-12 7:06am

Hi Please look at the solution updated under the heading "UPDATE 2 (After the comments)" and should work for you.

let me know the outcome

SruthiR 5-Apr-12 7:22am

yes. It is not giving any error now. But i am not getting the output. I have the date values in table still the output is showing null. Need to check. Thank you.

Bala Selvanayagam 5-Apr-12 7:31am

I can think about a reason why its null, the way you are executing the stored procedure does not look right.

Execute the stored procedure as follows and it should work

exec SampleP1