Problem changing data types in a SQL Server Table?

[blakemckenna]

I'm using SQL Server 2005. I'm trying to change the data type of a column from varchar(10) to smallDateTime in design mode. When I go to save the change...I get the following error message:

"- Unable to modify table.
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."

This table has 302 records in it. The column in question contains date values in the format of mm/dd/yyyy.

If I need to convert this over to a new table with the only thing being different is the column being defined now as a smallDateTime, I have NO idea how to write the SQL Code.

Please help!

Thanks,

[si_the_geek]

The problem is that it can't convert the Varchar values to Dates, presumably because one or more rows contains a value that isn't a valid date (such as "02/29/2007"), or because the format you are using isn't what SQL Server is expecting (that would surprise me, but is not impossible).

Instead of changing the existing column. try adding a new one, and then fill it using an Update statement (later you can delete the original, and rename the new one). You should be able to use CAST/CONVERT to do that, otherwise I think you will need to do a bit of string manipulation.

[blakemckenna]

Hey Si,

I did that and it worked....to a point. Instead of putting in just the date (mm/dd/yyyy) it inserted "mm/dd/yyyy 12:00:00 AM", which isn't what I want. I just want a 10 digit date value.

Any ideas?

Thanks,

[cheenu_vasan]

The conversation from one data type to another takes place only when the current value can adapt as such with the new data type without making any alteration.

If this is not possible then you need manually clear the values or take backup and update the field datatype and then update with the old values. You very well prepare the sql scripts to take backup, change data type and update them back to the original column.

[blakemckenna]

What I did was change a datatype in my Table from varchar(10) to smallDateTime. The values in the field before I made the change were in the format of mm/dd/yyyy. After I made the change, every record for that field appended 12:00:00 AM to the date value. That's not what I want. I'm trying now to strip out that time value within SQL Server Management Studio. Unfortunately, I'm terrible at writing SQL Code. How can I fix this problem?

Thanks,

[si_the_geek]

You can't, date data types (in many database systems, and in VB) also contain a time portion - you store it whether you want to or not.

What you can do is what VB does for you, which is display only the part you want. If you have a time of exactly midnight, VB will only show you the date (eg: MsgBox DateSerial(2008,1,20)+TimeSerial(0,0,0) ).

To do that, you need to format it - either in the SQL statement you use to get the data (using Cast/Convert), or in the client side where you display the data (for Classic VB you could use Format).

An important thing to note is that a formatted value is no longer a Date (it it a String), so cannot safely be used as a Date once you have formatted it.

[blakemckenna]

Si,

Here is my "Select" statement of my Stored Procedure that loads the table into a Grid on my form.

	SELECT	custNo,
			truckID,
			invoiceNo, 
			invoiceDate,
			Convert(smallDateTime, errorDate, 101),
			errorMsg,
			custLocNo,
			custLocAddr,
			custLocCity,
			custLocState,
			custLocZipCode,
			transDate, 
			productID,
			gallons, 
			prodPricePerGallon, 
			totalAmount,
			taxPerGallon,
			fedExciseTax,
			stateExciseTax,
			miscTax,
			salesTax,
			productCD,
			recordStatus,
			fm90fileName,
			originalCustNo,
			originalTruckID,
			rowID
	FROM    tblFM90Dump
   WHERE	(custNo BETWEEN @custNoStart AND @custNoEnd)				AND
			(truckID BETWEEN @truckIDStart AND @truckIDEnd)				AND
			(invoiceNo BETWEEN @invoiceNoStart AND @invoiceNoEnd)		AND
			(invoiceDate BETWEEN @invoiceDateStart AND @invoiceDateEnd)	AND
			(errorDate BETWEEN @errorDateStart AND @errorDateEnd)		AND
			(recordStatus BETWEEN @recordStatusStart AND @recordStatusEnd)
ORDER BY	custNo, truckID, invoiceNo

Everywhere where I reference this column field, "errorDate", I have changed it's Data Type to smallDateTime...even in my VB.Net app. When I go to run my application, I get this error (see attachment).

Thanks,

[si_the_geek]

Despite the text you add to the bottom of your error messages, please do not attach a Word document - not only does it mean we need to have Word installed (which is not always the case), but it also means that we need to spend extra time loading the file in a separate program (which usually isn't open to start with).

It is much better to simply post a picture file, or even better just the text from the error message.


In this case, the message is saying it can't convert a Char to a SmallDateTime.

As usual, the first step is to work out where the error might be happening. As it would be where something is being converted, it would most likely be one of these two lines:

Convert(smallDateTime, errorDate, 101),
(errorDate BETWEEN @errorDateStart AND @errorDateEnd) AND

Now you need to work out why it might be happening.

Presumably errorDate is your SmallDateTime field, so that should be OK (but check the help of Convert to make sure, and perhaps even run just that by itself in an SQL statement).

With the amount of experience you have with this sort of thing, you should be able to work out the likely issues (and solutions) for the other line.