Re: newbie: how convert nvchar to datetime?

Hari Prasad wrote: 
Hi,

USE ALTER TABLE with ALTER COLUMN

ALTER TABLE <table> ALTER COLUMN <Column_name> DATETIME
go

Thanks
Hari
SQL Server MVP


"Jim Muir" <jpmuirNoSpamPlease@xxxxxxxxx> wrote in message news:%23ycuY781FHA.3256@xxxxxxxxxxxxxxxxxxxxxxx

I have imported a table into msSQL (2000)
One of the fields which was stored originally as a simple date field (05/05/05) was created as nvarchar(8).

How to I alter this column to become a DateTime field type?
I want to do this before I add any more records.

The application will be sending a date like 10/23/2005 but without the time part.



I tried that in the past, but I could not get this to work. But your suggestion seemed simple and likely the cleanest way to get this going.

A little persistence revealed the problem...

I Got an error: Arithmetic Overflow Error converting expression to data type datetime. This has been the problem. I reviewed the table column, and noticed a few dates were messed up. I corrected the bad dates only to discover that SQL Enterprise queries don't support 'Alter'

But Query Analyzer worked and all 1006 rows were changed.

Thanks !




.

Relevant Pages

  • Re: sp_track_db_growth
    ... SQL Server MVP ... > I have added the above listed stored procedure to a number of my servers ... > have been able t use it quite successfully on most databases. ... > Arithmetic overflow error converting numeric to data type numeric. ...
    (microsoft.public.sqlserver.server)
  • Re: Alter Database Move column
    ... Tibor Karaszi, SQL Server MVP ... > Is it possible to move a column using an SQL script? ... > alter table MasterStationTest ...
    (microsoft.public.sqlserver.server)
  • Re: Alter Table for 39 million rows
    ... either way the disk space will have to be allocated and pages ... SQL Server MVP ... > I'm trying to alter a table column from tinyint to a smallint, however, the> table contains> 39 million rows and running the alter table takes a long ...
    (microsoft.public.sqlserver.programming)
  • Re: alter column but setting the default value
    ... SQL Server MVP ... "Sam Martin" wrote in message ... > alter table dbo.mytable ... > add mydatecolumn datetime null ...
    (microsoft.public.sqlserver.programming)
  • Re: Alter Table for 39 million rows
    ... a datatype to something larger should only be a metadata only change, ... try using just ALTER TABLE. ... dropping any indexes before the ALTER. ... > SQL Server MVP ...
    (microsoft.public.sqlserver.programming)