Change Data type from Nvarchar to datetime

i have a sql db in production and i need to change the data type of one of m
y
fields. Can this be done? I have made several attempts but have failed.Probably because you have data in there that is not a valid datetime. This
is a common symptom for using a catch-all datatype like nvarchar for storing
inappropriate data, whether it be to ease the development/validation of end
user interfaces or to seamlessly integrate legacy data.
"Failed" could mean many things to me. Are you just saying ALTER TABLE
tablename ALTER COLUMN columnname DATETIME? If so, and that fails due to a
conversion issue, then you may want to consider trying this:
-- how many are bad:
SELECT COUNT(*) FROM TableName WHERE IsDate(ColumnName)=0;
-- a few samples:
SELECT TOP 50 ColumnName FROM TableName WHERE ISDate(ColumnName)=0;
You might be really hosed, or you my just have to tweak the data before
altering the table, by converting to a valid datetime format. It's tough to
get any more specific without valid DDL and sample data (see
http://www.aspfaq.com/5006
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:6BB5D430-FFF8-4BC9-BF59-DA27B3B83615@.microsoft.com...
>i have a sql db in production and i need to change the data type of one of
>my
> fields. Can this be done? I have made several attempts but have failed.|||Thank you for your suggestion. However, I do not have any data in this
particular field. This is the error I get...
'Contacts' table
- Warning: Data might be lost converting column 'MaritalStatus' from
'nvarchar(20)'.
The MaritalStatus field does not have any data. I would like to rename it
to PromoCodeEmail and change the data type to datetime.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Probably because you have data in there that is not a valid datetime. Thi
s
> is a common symptom for using a catch-all datatype like nvarchar for stori
ng
> inappropriate data, whether it be to ease the development/validation of en
d
> user interfaces or to seamlessly integrate legacy data.
> "Failed" could mean many things to me. Are you just saying ALTER TABLE
> tablename ALTER COLUMN columnname DATETIME? If so, and that fails due to
a
> conversion issue, then you may want to consider trying this:
> -- how many are bad:
> SELECT COUNT(*) FROM TableName WHERE IsDate(ColumnName)=0;
> -- a few samples:
> SELECT TOP 50 ColumnName FROM TableName WHERE ISDate(ColumnName)=0;
> You might be really hosed, or you my just have to tweak the data before
> altering the table, by converting to a valid datetime format. It's tough
to
> get any more specific without valid DDL and sample data (see
> http://www.aspfaq.com/5006
>
>
>
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:6BB5D430-FFF8-4BC9-BF59-DA27B3B83615@.microsoft.com...
>
>|||Joe,
First, thats a warning, not an error. The warning is generated because
changing data types can result in loss of data, but that doesn't necessarily
mean they will.
Second, if you have a column that is not used, drop the column and add a new
column. There is no need to rename it and change the data type.
You are best off using a script for these changes rather than using the gui.
Scripts can be run against your development and test databases and debugged
before runnign them against production. Scripts also insure that the
changes made in all databases are the same, without having to worry about
user error from clicking on the wrong field in enterprise manager.
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:82D1F9C5-9AF9-4D3E-AC46-886F0E64CB4B@.microsoft.com...
> Thank you for your suggestion. However, I do not have any data in this
> particular field. This is the error I get...
> 'Contacts' table
> - Warning: Data might be lost converting column 'MaritalStatus' from
> 'nvarchar(20)'.
> The MaritalStatus field does not have any data. I would like to rename it
> to PromoCodeEmail and change the data type to datetime.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
This
storing
end
to a
tough to
of
failed.|||I have never written a script before. Is there some literature that will
assist me in doing you suggested?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Probably because you have data in there that is not a valid datetime. Thi
s
> is a common symptom for using a catch-all datatype like nvarchar for stori
ng
> inappropriate data, whether it be to ease the development/validation of en
d
> user interfaces or to seamlessly integrate legacy data.
> "Failed" could mean many things to me. Are you just saying ALTER TABLE
> tablename ALTER COLUMN columnname DATETIME? If so, and that fails due to
a
> conversion issue, then you may want to consider trying this:
> -- how many are bad:
> SELECT COUNT(*) FROM TableName WHERE IsDate(ColumnName)=0;
> -- a few samples:
> SELECT TOP 50 ColumnName FROM TableName WHERE ISDate(ColumnName)=0;
> You might be really hosed, or you my just have to tweak the data before
> altering the table, by converting to a valid datetime format. It's tough
to
> get any more specific without valid DDL and sample data (see
> http://www.aspfaq.com/5006
>
>
>
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:6BB5D430-FFF8-4BC9-BF59-DA27B3B83615@.microsoft.com...
>
>|||What part of my suggestion are you having difficulty with?
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:356065F7-EB6A-4304-9448-ECF8B0FB40F7@.microsoft.com...
>I have never written a script before. Is there some literature that will
> assist me in doing you suggested?
> "Aaron Bertrand [SQL Server MVP]" wrote:
>