Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value. Expand / Collapse
Author
Message
Posted Monday, June 9, 2014 11:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 4:56 PM
Points: 7, Visits: 17
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
Post #1579040
Posted Tuesday, June 10, 2014 12:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 13,312, Visits: 10,178
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1579058
Posted Tuesday, June 10, 2014 4:50 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 15,527, Visits: 27,909
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1579123
Posted Tuesday, June 10, 2014 6:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:36 PM
Points: 36,769, Visits: 31,226
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1579134
Posted Tuesday, June 10, 2014 4:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 4:56 PM
Points: 7, Visits: 17
HI SSC-Dedicated,

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

Post #1579439
Posted Tuesday, June 10, 2014 4:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 4:56 PM
Points: 7, Visits: 17
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.

Post #1579444
Posted Wednesday, June 11, 2014 12:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 13,312, Visits: 10,178
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1579469
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse