Q
Problem solve Get help with specific problems with your technologies, process and projects.

Date conversion in SQL Server 7 Enterprise Edition

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

set @dfdate=(convert(varchar(10),@dfdate,110))

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:

UPDATE ORDERS
SET FieldSmallDateTime = FieldChar

Or:

DECLARE @Smalldt AS SMALLDATETIME
DECLARE @Chart AS CHAR(10)
SET @Chart = '07-31-1967'
SET @Smalldt = @Chart
SELECT @Smalldt

With Convert:

UPDATE ORDERS
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)
UPDATE ORDERS
SET FieldSmallDateTime = @dfdate
WHERE OrderID = 10252

I hope this helps you solve your problem!

 

For More Information

This was last published in March 2001

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close