I am tryng to convert the string "07-31-1967" in SQL Server 7 Enterprise Edition to smalldatetime I get the following 296 error:
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value
How do I do this?
Let's assume that you declare the variable @dfdate as a SmallDateTime type. Then the Convert function should work this way:
CONVERT (data_type[(length)], expression [, style])
data_type: This is the data type format of the source data that you want to convert.
length: This is the length (optional) for char and binary data types, like char, varchar, etc.
expression: This is any valid SQL Server expression, in this case the variable or field of your source data, the one that you want to assign. In your case is wrong, because it could not be the same.
style: This is a format that you supply for the conversion but, just apply when we want to convert from DateTime, SmallDateTime, Numeric Types to types that manage chars or strings, so it also does not apply in your case.
Remember, the conversion between char, nchar, varchar, nvarchar, datetime and smalldatetime is implicit, meaning that you do not need to use the Convert or Cast functions.
Use the following samples to do your conversion; however, you must be sure of the type and length of your fields. First, let's assume that:
FieldSmallDateTime is SmallDateTime Type and Length (4)
The easy way:
SET FieldSmallDateTime = FieldChar
DECLARE @Smalldt AS SMALLDATETIME
DECLARE @Chart AS CHAR(10)
SET @Chart = '07-31-1967'
SET @Smalldt = @Chart
SET FieldSmallDateTime = Convert(varchar(10), FieldChar)
With variables and a little more dynamic:
DECLARE @dfdate AS CHAR(10)
SET @dfdate = (SELECT FieldChar FROM ORDERS WHERE OrderID= 10252)
SET FieldSmallDateTime = @dfdate
WHERE OrderID = 10252
I hope this helps you solve your problem!
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL Server guru is waiting to answer your toughest questions.
Dig Deeper on Microsoft SQL Server Installation
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.