The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value.

Question

The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value.

BokBob

SSChasing Mays

Points: 613
More actions
June 9, 2014 at 11:53 pm

#292158

hi All,
My below proc gives me this error.. "The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value."
Please help...
===================================================================
alter PROCEDURE [dbo].[Consent_Update]
@ID int,
@ConsentRecieved smalldatetime
AS
BEGIN
UPDATE [BreastCancer_Test].[dbo].[tblParticipant]
SET
[BloodConsentRecieved] = CONVERT(SMALLDATETIME,@BloodConsentRecieved)
where ID =@ID
END
GO

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

This is an overflow error. You need to check the value that is passed to the stored procedure and verify it is an actual date.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 2

SMALLDATETIME only runs January 1, 1900, through June 6, 2079. It sounds like the value you're trying to convert falls outside that range. Or, as Koen already said, it's not a date at all.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

BokBob (6/9/2014)
hi All,
My below proc gives me this error.. "The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value."
Please help...
===================================================================
alter PROCEDURE [dbo].[Consent_Update]
@ID int,
@ConsentRecieved smalldatetime
AS
BEGIN
UPDATE [BreastCancer_Test].[dbo].[tblParticipant]
SET
[BloodConsentRecieved] = CONVERT(SMALLDATETIME,@BloodConsentRecieved)
where ID =@ID
END
GO

As it is currently edited, it will never work because @BloodConsentRecieved is undefined. It's pretty tough to eat a rolling donut. I suggest you stop editing your original post in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BokBob SSChasing Mays Points: 613 More actions · Answer 4

HI SSC-Dedicated,

I did edit it because there were spelling mistakes in my post too..

BokBob SSChasing Mays Points: 613 More actions · Answer 5

hi All,

Thank you all for your inputs.. I was able to solve the issue by changing the parameter type as date instead of smalldatetime.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 6

BokBob (6/10/2014)
hi All,
Thank you all for your inputs.. I was able to solve the issue by changing the parameter type as date instead of smalldatetime.

So it probably was a date out of bound as Grant suggested.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP