July 1, 2011 at 3:39 pm
I have an Employee table in SqlSvr2008r2. To keep this simple, let's say it has two fields: EmpID and BirthDate. Several other tables have foreign keys linking to EmpID which is a primary key. When building the Employee table, I gave BirthDate the wrong data type. I tried opening the table in design view, but was not able to save after making the change. I'm a rank beginner with TSQL, but I tried scripting the table to a new query window and changing CREATE to ALTER...didn't work (it's okay to laugh).
I do know how to do this: break the links, drop the table, rebuild it with the correct data types, reestablish the links, reload the data. I know (from experience, believe it or not) this works.
Can someone please tell me an easier way?
July 1, 2011 at 4:30 pm
Welcome maddoxd. You can change the data type of the BirthDate column without dropping and recreating the table, or even bothering with the Primary Key on EmpID. Something like this should work...
ALTER TABLE Employee ALTER COLUMN BirthDate [new data type here] [NULL | NOT NULL] ;
...provided a couple things are true:
1. the data in the column can be converted to the destination data type
2. there are no other constraints or indexes referring to the column
3. if you make the column NOT NULL there are no NULLs present
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 5, 2011 at 1:14 pm
EUREKA! On checking the quality of the data in the field in question, I found values outside the expected range (and out of range for the datetime data type). I corrected those values and the query ran fine. Next time I'll check that first!!! :hehe:
Pre-Eureka post:
===============================================================================================================
Thanks for your response. Of the three conditions you listed, I am sure #2 and #3 are met. As to #1, I am trying to change a date to a datetime.
When I run the following from the query editor window:
ALTER TABLE [dbo].[LstEmpEmployee] ALTER COLUMN [LstEmpDateBirth] [DateTime] NULL;
I get the following result:
Msg 242, Level 16, State 3, Line 1
The conversion of a date data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
Just for reference, the original field specification is:
CREATE TABLE [dbo].[LstEmpEmployee](
•
•
•
[LstEmpDateBirth] [date] NULL,
•
•
•
July 5, 2011 at 1:24 pm
You need to make sure that all the data is compatible with a datetime. Now, the only reason a value in a DATE column would not be castable to DATETIME would be if it's before 1753 (which shouldn't happen for birthdates)
Check the range of values in the column, that's about the only thing it could be.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply