Conversion failed when converting datetime from ch

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conversion failed when converting datetime from ch

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-12-10 : 11:53:56

I've been trying to create a stored procedure that can perform any CRUD action based on a parameter value, to save creating a seperate procedure for each action.

I'm using the following procedure, but get an error whenever I try to use it for Deleting:

Conversion failed when converting datetime from character string

Can anyone spot whatever might be causing this problem? I can't see how the DELETE part of the command could cause this error, because the dateAdded column isn't even being used...

I've shorted the overall code to make it more legible, but will post the full version if the problem is not obvious:


ALTER PROCEDURE [mysp_editList]

	@action nvarchar(150) = NULL,
	@ID int = NULL,
	@CustomerMessage nvarchar(4000) = NULL,
	@dateAdded datetime = GETDATE
	
AS

	IF @action = 'insert'
		BEGIN
			INSERT INTO tblMyTable
				(
				CustomerMessage
				)
			VALUES
				(
				@CustomerMessage
				)
		END
	ELSE IF @action = 'update'
		BEGIN
			UPDATE
				tblMyTable
			SET
				CustomerMessage = @CustomerMessage,
				dateAdded = @dateAdded
			WHERE
				ID = @ID
		END
	ELSE IF @action = 'delete'
		BEGIN
			DELETE FROM
				tblMyTable
			WHERE
				(ID = @ID)
		END
	ELSE IF @action = 'select'
		BEGIN
			SELECT
				*
			FROM
				tblMyTable
			WHERE
				(ID = @ID OR @ID IS NULL)
			ORDER BY
				dateAdded DESC
		END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 12:00:25

what's the datatype of dateadded field?

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-12-11 : 03:59:05

Its a datetime field, with default value of getDate

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-11 : 04:10:06

Which line is producing the error?
Maybe it's the calling code that wants to execute the procedure above that throws the error?
If you have string/varchar parameter and want to call a datetime parameter, make sure you use iso format for string "yyyy-mm-dd".

E 12°55'05.63"
N 56°04'39.26"

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-12-11 : 04:30:40

Hi

The error is definitely thrown in SQL. When I execute this query in Management Studio:

exec mysp_editList @ID=3,@action=N'delete'

I get this error:

Msg 241, Level 16, State 1, Procedure mysp_editList, Line 0
Conversion failed when converting datetime from character string.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-12-11 : 04:43:08

u can't assign a default of GETDATE while declaring the parameters
try like this

ALTER PROCEDURE [mysp_editList]

	@action nvarchar(150) = NULL,
	@ID int = NULL,
	@CustomerMessage nvarchar(4000) = NULL,
	@dateAdded datetime = NULL
	
AS
        SELECT @dateAdded = GETDATE() WHERE @dateAdded IS NULL
	IF @action = 'insert'
		BEGIN
			INSERT INTO tblMyTable
				(
				CustomerMessage
				)
			VALUES
				(
				@CustomerMessage
				)
		END
	ELSE IF @action = 'update'
		BEGIN
			UPDATE
				tblMyTable
			SET
				CustomerMessage = @CustomerMessage,
				dateAdded = @dateAdded
			WHERE
				ID = @ID
		END
	ELSE IF @action = 'delete'
		BEGIN
			DELETE FROM
				tblMyTable
			WHERE
				(ID = @ID)
		END
	ELSE IF @action = 'select'
		BEGIN
			SELECT
				*
			FROM
				tblMyTable
			WHERE
				(ID = @ID OR @ID IS NULL)
			ORDER BY
				dateAdded DESC
		END

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-12-11 : 11:45:47

Hi PeterNeo

Thank you for posting. Yes, your suggestion has stopped the error.

Could you please englighten me as to why the procedure was working for the 'insert' action, and failing on the 'delete' action?

I would have thought it would always fail if the values were being set incorrectly?!?

X002548
Not Just a Number

15586 Posts

Posted - 2008-12-11 : 12:21:07

tblMyTable

too funny

and "R" as a login?

In any case, what sense does getdate() have here?

Just use GetDate()

Also, I'm not a big fan of 1 size fits all sprocs

Make 3 separate ones...to me it's a best practice, if only because you don't have to regression test all of those DML operations if you change 1 line of code.

MOO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-12-11 : 12:38:20

quote:
tblMyTable ... and "R" as a login?

Yes it is a bit shady isn't it. It was because I was posting schema data about my product a while back and didn't want any competitors to realise who I was and be able to copy it. Not much chance of that happening but why take the risk.

quote:
In any case, what sense does getdate() have here?

Use of getDate() within the procedure itself was just to reinforce the logic I already had in place. Given that your suggestion fixed the issue then it was really overkill.

I'm still perplexed as to why the procedure would throw an error on the delete logic path but succeed with the other paths.

In the future I'll take your advice and create separate procedures. This (lazy) approach has proved to be a pain in the ass.

X002548
Not Just a Number

15586 Posts

Posted - 2008-12-11 : 13:27:15

See now, to me, lazy (and I like lazy), would be to have 1 sproc per functionality.

if it doesn't change, you don't have to revisit it.

Make a change to yours, and you have 3 times the level of testing

also, contrary to popular belief in the dating world, small is good here

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2008-12-11 : 14:11:38

And I hate ELSE IF

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-12-12 : 04:20:47

Brett,

Thanks for sharing some of your wisdom with me... :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources